check constraint for date column as format wise check its an valid date during the insertion

Question

I want a check constraint. That checks the date column, whether date is in yyyy-MM-dd format or not.

I have no idea. But I simply tried,

create table #date( dob date check (dob like 'yyyy-MM-dd'))

insert #date values( '2018-09-24')

So date conversion error is remains.

Update 1

Note:

1. @@version: Microsoft SQL Server 2012
2. If date column is in varchar, then it is also welcome.
3. Some answers are containing the convert(). But my scenario was, front end teams are inserting the values as dd-MM-yyyy. I told many time that insert correct format as yyyy-MM-dd (as per our procedure). But they cant. so I want to restrict their inserting values according to format.

Thanks in advance.

TamilPugal.

Answer 1

DATE is not stored internally as string so you cannot use CHECK constraint.

create table #date(dob date);

'yyyy-MM-dd' is only presentation matter.

As for insert you could use:

INSERT INTO tab(col) VALUES ('20180101');  -- 'YYYYMMDD' culture independent
INSERT INTO tab(col) VALUES (CONVERT(DATE, 'string', style));

CONVERT

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Answer 2

create table #date( dob date check  
 (dob like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'));

 insert into #date values('2018-08-20')

Try this to get the expected output.

Answer 3

DATE should be considered a binary sortable type.

Definitely: Store it with a prepared statement, using a Date object is some programming language.

Insertion of string literals is unfortunately in the non-standard format YYYYMMDD:

'20181231' for 2018-12-31

For display again as ISO standard date, YYYY-MM-DD, use the predefined 23.

SELECT CONVERT(VARCHAR, dob, 23) ...