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

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

yyyy-MM-dd

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:

@@version: Microsoft SQL Server 2012

date

varchar

convert()

dd-MM-yyyy

yyyy-MM-dd

Thanks in advance.

TamilPugal.

3 Answers
3

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.

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

DATE

CHECK

create table #date(dob date);


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

'yyyy-MM-dd'

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 ] )

convert()

check constraint

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:

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) ...


convert()

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.