Click here to Skip to main content
14,734,263 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I'm on Sql Server and I have two values of type DATE inside my table, Date1 and DAte2.

Date1 is the current date by default (I added a Default Constraint to it).
I want Date2 to be incremented by 5 days (Date1 + 5 days) by default, and to check it.

What I have tried:

I've been trying to do the following but I got errors:


1)
<pre>ALTER TABLE Workshops
ADD CONSTRAINT CK_Workshops_EndDate5daysAfterStartDate
                   CHECK (EndDate = StartDate + 5);

I could not complete the operation because date and int are different value types



2)
ALTER TABLE Workshops
ADD CONSTRAINT DF_EndDate_5daysAfterStartDate
                    DEFAULT DATEADD(day, 5, StartDate )

I got this error:
"The name "StartDate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."


3)
<pre>ALTER TABLE TableName
ADD CONSTRAINT CK_TableName_Date2Is5DaysAfterSTartDate
        CHECK (Date2= DATEADD(day, 5, StartDate));



I got this error:

The ALTER TABLE statement conflicted with the CHECK constraint "CK_TableName_Date2Is5DaysAfterSTartDate". 
Posted
Updated 4-Nov-20 0:44am
v2

Look at the DATEADD function: SQL Server DATEADD() Function[^]
   
Comments
xhon 4-Nov-20 2:26am
   
I also tried this:


ALTER TABLE TableName
ADD CONSTRAINT CK_TableName_Date2Is5DaysAfterSTartDate
        CHECK (Date2= DATEADD(day, 5, StartDate));



I got this error:

The ALTER TABLE statement conflicted with the CHECK constraint "CK_TableName_Date2Is5DaysAfterSTartDate". 
OriginalGriff 4-Nov-20 3:52am
   
And if you google the error message, it's obvious why:
https://www.google.com/search?q=alter+table+statement+conflicted+with+the+check+constraint&oq=ALTER+TABLE+statement+conflicted+with+the+CHECK+constraint&aqs=chrome.0.0i457j0i22i30.991j0j7&sourceid=chrome&ie=UTF-8

A quick check will tell you that the constraint you are trying to apply fails with the data already in your DB. So check your DB and sort that out first!
xhon 4-Nov-20 5:50am
   
ok, I created the tables from scratch and now it works. I still have the problem 2, though...I can't add a default constraint to the Date2 in order to force it to be Date1 + 30 days
OriginalGriff 4-Nov-20 6:01am
   
Column constraints cannot reference other columns as SQL doesn't know in what order the data will be supplied!
You probably need a Trigger instead:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15
A CHECK constraint will reject any data which doesn't match the constraint. If you try to insert / update data which is not exactly five days apart, the data will be rejected.

A DEFAULT constraint cannot access data, whether from the same row, another row in the same table, or another table.

There is nothing built-in which will do what you want. You will either need to calculate and insert the default value when you create the record, or set the column to be nullable and calculate the default value when you read the record.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900