Post reply

Date Constraint

  • October 8, 2003 at 11:02 am

    #84609

    Hello,

    I need a Check Constraint on a date field (smalldatetime) that will check for a date range between the current date (GETDATE()) and two years into the future.

    How would I express such a constraint?

    Thanks for your help!

    CSDunn

  • October 8, 2003 at 11:47 am

    #477607

    If by "the current date" you mean including the current time, then just:

    

    CHECK (ColumnName BETWEEN GETDATE() AND DATEADD(yy,2,GETDATE()))

    Otherwise, perhaps something like this:

    

    CHECK (ColName >= CONVERT(char(8),GETDATE(),112)

     AND ColName < DATEADD(yy,2,CONVERT(char(8),GETDATE(),112))+1)

    --Jonathan



    --Jonathan

  • October 8, 2003 at 11:54 am

    #477608

    Something like this might work:

    ALTER TABLE tablename

    ADD datefield DATETIME CHECK

    (datefield > GETDATE() AND datefield < GETDATE()+730)

    -SQLBill

  • October 8, 2003 at 11:58 am

    #477609

    Oooo, Jonathan beat me to the answer. Must have been just by a second or two. (BTW-his suggestion is better than mine).

    -SQLBill

  • October 14, 2003 at 11:13 am

    #478188

    Thanks for your help!

    CSDunn

  • October 15, 2003 at 4:39 am

    #478263

    I would like to point out that Jonathan is converting to the ISO date format which is better than other date formats as it is not likely to fail when compared. Good job.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply