Microsoft SQL Constraint Check for Year higher than 2000-CodePudding

I'm creating a database and I want to create a Constraint Check that only allows to insert Dates that are after year 2000.

This is where i store dates:

CREATE TABLE User (
username varchar(20) NOT NULL,
birthdate DATE NOT NULL,
CONSTRAINT user_birthdate_ck CHECK (birthdate > 2000)
)

CodePudding user response：

2000 is not a date, it's a number.

You need to specify a date in quotes using an unambiguous date format. Best to use 'YYYYMMDD'.

CREATE TABLE [User] (
  username varchar(20) NOT NULL,
  birthdate DATE NOT NULL,
  CONSTRAINT user_birthdate_ck CHECK (birthdate > '20000101')
);

db<>fiddle

Assuming you actually wanted after the year 2000 then you probably want

  CONSTRAINT user_birthdate_ck CHECK (birthdate >= '20010101')

Do not use > '20001231' if you have a time component as it won't be correct.

CodePudding user response：

Change the year to a properly formed date as follows:

CONSTRAINT user_birthdate_ck CHECK (birthdate > '2000/12/31')

It would also be prudent to explicitly set the date format being used before the CREATE TABLE as follows:

SET DATEFORMAT ymd;  
GO

CodePudding user response：

BEWARE all the strings expression of dates showns in answers are not in the ISO SQL format and won't do the job under certain session parameters like language or some others, espcially mixed with non Transct SQL in a batch...

The only string format that never cause you any trouble is the SQL ISO long date format as :

'AAAA-MM-JJ'