prathapa reddy prathapa reddy - 3 months ago 19
SQL Question

Sql server 2012 constraint to check dates

I'm trying to insert a constraint to a table to check whether the order date is less than manufactured date of the product when data being inserted into the table. But the statement is throwing an error.
Can somebody please help me?

create table dateTime1 (orderDate date, mfd date, constraint check_date check orderDate < mfd);

error Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'orderdate'.


I tried to use the function datediff(). But I'm getting the error for that also.

create table dateTime1 (orderDate date, mfd date, check DATEDIFF(yy,orderDate,mfd) > 0);

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DATEDIFF'.

Answer

You need parentheses around the condition for a check constraint:

create table dateTime1 (
    orderDate date,
    mfd date,
    constraint check_date check (orderDate < mfd)
);

Note: the datediff() logic is different from the comparison logic. datediff(year) counts the number of year boundaries between two dates . . . so 2015-12-31 and 2016-01-01 are one year apart whereas 2016-01-01 and 2016-12-31 are 0 years apart.

Comments