I am creating a Customers table and in order to add a Customer, the customer has to be over 21. I have added a CHECK on the date of birth. Its working fine for all dates except the exact date 21 years ago. The logic seems correct. I don’t know why its doing that.
CREATE TABLE customers(
customer_ID int,
dob date CHECK (dob < DATEADD(YEAR, -21, GETDATE()))
);
INSERT INTO customers VALUES(1, '09/26/2003');
–works as expected.
INSERT not allowed.
INSERT INTO customers VALUES(1, '09/25/2003');
–This should be blocked. But it still allows INSERT
7
GETDATE(), its name notwithstanding, returns the current datestamp, including time.
Do you want to do the operation on just the current date?
dob date CHECK DATEADD(year, -21, CONVERT(DATE, GETDATE()))
1