March 5, 2003 at 12:48 pm
Hello,
I have not been able to quickly find an example of how to set up a CHECK constraint on a table column where the datatype is either 'smalldatetime' or 'datetime'. I have a couple of columns that store date data, both are of data type 'datetime'. One column needs to check that dates are greater than 1/1/1980, and the other needs to check for > 1/1/1980 and < 1/1/2005.
I attempted to set up the first check constraint I mentioned (> 1/1/1980)like so:
ALTER TABLE [dbo].[tblCLELStudData]
ADD CONSTRAINT [CN_DateElServices]
CHECK ([DateElServices] > 'January 1, 1980')
I also tired to express the date as '1/1/1980' as the condition of the CHECK. SQL Server 2000 allowed me to set up the constraint, but when I tested the column for an update with a date earlier than the CHECK condition, I found that the constraint was not enforced.
How do I express single dates and date ranges in a CHECK constraint?
Thanks.
CSDunn
March 5, 2003 at 12:59 pm
quote:
...when I tested the column for an update with a date earlier than the CHECK condition, I found that the constraint was not enforced...
We tested this :
CREATE TABLE tblCheckDate
(dtmDate smalldatetime NOT NULL CONSTRAINT CH_Date CHECK (dtmDate>='January 1, 1980'))
INSERT tblCHeckDate VALUES ('1/1/81')
INSERT tblCheckDate VALUES ('1/1/79')
UPDATE tblcheckdate set dtmDAte='1/1/78' where dtmdate='1/1/81'
Here's an example of date range constraint:
CREATE TABLE tblCheckDate
(dtmDate smalldatetime NOT NULL CONSTRAINT CH_Date CHECK (dtmDate BETWEEN 'January 1, 1980' AND 'January 1, 2050'))
Edited by - RonKyle on 03/05/2003 1:02:12 PM
March 5, 2003 at 1:07 pm
It turns out that I had a mismatch in the constraint I was testing vs. the one I was altering. I'm having to develop this application and answer calls on our MS Office support help line today at the same time. When will the job market improve?
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply