CODEWITHSUNDEEP
sqlsql-server
David Talbot
David Talbot

Reputation: 7

Sql server date constraint

alter table dbo.transactions
add constraint tk_paiement check ([Date_paiementdu]=[dateTransaction] +(30))

i want date_paiementdu to be set 30 day after the datetransaction but its not working

need some help! thanks

Upvotes: 0

Views: 370

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

One of the principles of database normalization is to not store calculated values. With that in mind, you can probably achieve your goal with something like this:

where datediff(day, dateTransaction, Date_paiementdu) <= 30

The details of how to accomplish this are RDBMS specific, and you didn't specify yours.

Upvotes: 0

GarethD
GarethD

Reputation: 69769

Sounds like you want a computed column rather than a check constraint:

ALTER TABLE yourTable 
ADD Date_paiementdu AS DATEADD(DAY, 30, dateTransaction);

If you want the column to be not nullable or indexed then you will need to use PERSISTED and also DateTransaction will need to be not nullable:

ALTER TABLE yourTable 
ADD Date_paiementdu AS DATEADD(DAY, 30, dateTransaction) PERSISTED NOT NULL;

To clarify further using PERSISTED will actually store the data that is to be displayed in the column, omitting it will keep the column as a virtual column that is calculated each time it is needed.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300579

You have defined a CHECK constraint, but what you appear to want is a computed column:

alter table dbo.transactions 
add tk_Date_paiementdu as (DateAdd(day, 30, [dateTransaction])

Upvotes: 2

Related Questions