+ Reply to Thread
Results 1 to 2 of 2
  1. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #1

    Default T-SQL EXTRACT(YEAR FROM....

    I am running through the examples in the book SQL Puzzles by Joe Celko and I am working on the first puzzle. As of the moment I am looking at the first answer which he gives as one possible implementation.

    ---
    CREATE TABLE FiscalYearTable3
    (fiscal_year INTEGER NOT NULL PRIMARY KEY,
    start_date DATE NOT NULL,
    CONSTRAINT valid_start_date
    CHECK(( (YEAR FROM start_date) = fiscal_year - 1)
    AND (EXTRACT (MONTH FROM start_date) = 10)
    AND CHECK (EXTRACT (DAY FROM start_date) = 01)),
    end_date DATE NOT NULL,
    CONSTRAINT ((EXTRACT (YEAR FROM end_date) = fiscalyear)
    CHECK ((EXTRACT (YEAR FROM end_date) = fiscal_year)
    AND (EXTRACT (MONTH FROM end_date) = 09)
    AND (EXTRACT (DAY FROM end_date) = 30)));
    ---
    Now when I run this I get the error:
    sqltest(SQLTEST\Administrator): Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'FROM'.

    And I believe that is because the EXTRACT() function is not supported. How could I alter this to get the desired results?

    I assume I could use DATEPART but I have yet to work out how...
    Reply With Quote Quote  

  2. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #2
    Sorry guys. This was a syntax error. The following code worked fine due to removing the word CHECK from line 7.

    CREATE TABLE FiscalYearTable4
    (fiscal_year INTEGER NOT NULL PRIMARY KEY,
    start_date DATE NOT NULL,
    CONSTRAINT valid_start_date
    CHECK((DATEPART(YEAR, start_date) = fiscal_year - 1)
    AND (DATEPART (MONTH, start_date) = 10)
    AND (DATEPART(DAY, start_date) = 01)),
    end_date DATE NOT NULL,
    CONSTRAINT valid_end_date
    CHECK ((DATEPART (YEAR, end_date) = fiscal_year)
    AND (DATEPART (MONTH, end_date) = 09)
    AND (DATEPART (DAY, end_date) = 30)));
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks