How to check that a DATE is incremented by N days (int)?

Question

0.00/5 (No votes)

See more:

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 3-Nov-20 19:46pm

xhon

Updated 3-Nov-20 23:44pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-11-03T20:11:00

Solution 1

Look at the DATEADD function: SQL Server DATEADD() Function[^]

Posted 3-Nov-20 20:11pm

OriginalGriff

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

Richard Deeming · Answer 2 · 2020-11-03T23:45:00

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.