How to check whether a date is any of the valid date types present in SQL Server 2012? -

the code below validating date types present in mm/dd/yyyy format , should not contain time, how check date field's validity in possible formats sql server offers?

declare @dateofbirth varchar(max); declare @dateofbirth_flag bit;     if(isdate(@dateofbirth)=1 , @dateofbirth not '%[:]%')     begin         set @dateofbirth_flag=1;     end     else     begin          set @dateofbirth_flag=0;     end 

in general - if ever possible:

you should avoid culture specific date/time literals , better use appropriate data types!

your question has no plain answer all possible formats sql server offers: date 01/13/2016 valid in mdy culture, invalid, if culture dmy. more dangerous: if date 03/05/2016 valid, differing values there...

in formats without year 03/05/08 you'll have pure chaos.

you might extract details this

select * sys.syslanguages 

what want tell: have know , have specify culture. never rely on implicit casting! checked valid date might wrong input...

but in luck, since using sql server 2012. there new try_parse , try_convert can specifiy target culture. valid date/time or null if cast fails.

on linked page you'll find try_convert can specify format via style...

check apporach try_convert:

declare @dateofbirth varchar(max)='12/13/2016'; declare @dateofbirth_flag bit;  set @dateofbirth_flag=case when try_convert(date,@dateofbirth,101) null 0 else 1 end select @dateofbirth_flag  --the result: `1` style `101`      set @dateofbirth_flag=case when try_convert(date,@dateofbirth,102) null 0 else 1 end select @dateofbirth_flag  --the result: `0` style `102`