Options
T-SQL EXTRACT(YEAR FROM....
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
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...
---
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...
Comments
-
OptionsRobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
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)));