0

How can I add a check constraint to make sure that a date is equal to Today or within the last year and another check constraint to ensure that dateA is equal to dateB or earlier by a maximum of 3 days ?

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by Aussie_Here
0

You have to use a trigger for this and not a constraint -- constraints can't check other column values. I tried the constraint approach and it did not work with referencing another column:

IF OBJECT_ID('TempTable', 'U') IS NOT NULL DROP TABLE TempTable
Create Table TempTable
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  Date1 DateTime,
  Date2 DateTime CHECK(Date2 >= DateAdd(year, -1, GetDate()) and Date2 <= GetDate())
)
GO
Insert Into TempTable (Date1, Date2) Values (GetDate(), GetDate()-1)

I'll help you with a trigger after lunch if you're still having problems.

0

Thanks a lot I got it work using check constrain from visual database studio((without any query )). Thanks again for your prompt reply

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.