if object_id('dbo.tablename','U') is not null begin drop table dbo.tablename end

go

create table dbo.tablename(

IDintnot null identity(1,1) primary key clustered,

SDatevarchar(10) not null

)

go

ALTER TABLE dbo.tablename ADD CONSTRAINT CHK_Date_format check

(case when SDate not like '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' then 0

when isdate(SDate) <> 1 then 0

else 1 end = 1)

go

insert into dbo.tablename select sdate = '2014/01/01'

go

insert into dbo.tablename select sdate = '2014/13/01'

go

insert into dbo.tablename select sdate = '2014/1/01'

go

insert into dbo.tablename select sdate = '2004/02/29'

go

insert into dbo.tablename select sdate = '2005/02/29'

go

select * from dbo.tablename

go

if object_id('dbo.tablename','U') is not null begin drop table dbo.tablename end

Results:

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

ID SDate

----------- ----------

1 2014/01/01

4 2004/02/29

(2 row(s) affected)