I need to check datecom is less than datelivr

create table Commande
Numcom int identity primary key,
Datecom date,
Datelivr date,
Codecli int foreign key references clients (Codecli) 

alter table Commande 
    add constraint date_check check(datediff(day,Datecom,Datelivr) > 0)

but I get the error message:

The ALTER TABLE statement conflicted with the CHECK constraint "date_check". The conflict occurred in database "tp4", table "dbo.Commande".

How can this happen?


The error message is self-explanatory: Your check constraint is trying to enforce that all values in Datecom are at least one day earlier than Datelivr, but you must have at least one row where this is not true - either Datecom is the same day, later, or one of the values is NULL. Find those rows using:

SELECT Numcom, Datecom, Datelivr
FROM dbo.Commade
WHERE datediff(day,Datecom,Datelivr) <= 0
  OR Datecom IS NULL
  OR Datelivr IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.