Siddharth Bhardwaj Siddharth Bhardwaj - 1 year ago 75
SQL Question

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

The code below is validating only date types present in

format and it should not contain time, but how to check a date field's validity in all the possible formats that SQL Server offers?

declare @dateofbirth varchar(max);
declare @dateofbirth_flag bit;
if(ISDATE(@dateofbirth)=1 and @dateofbirth not like '%[:]%')
set @dateofbirth_flag=1;
set @dateofbirth_flag=0;

Answer Source

In general - if ever possible:

You should avoid culture specific date/time literals and better use the appropriate data types!

Your question has no plain answer for all the possible formats that SQL Server offers: A date 01/13/2016 is valid in a mdy culture, but is invalid, if the culture is dmy. Even more dangerous: If the date is 03/05/2016 it is valid, but you will get differing values from there...

In formats without a year like 03/05/08 you'll have pure chaos.

You might extract details with this

select * from sys.syslanguages

What I want to tell: You have to know and you have to specify the culture. Never rely on any implicit casting! Even a checked valid date might be a wrong input...

But you are in luck, since you are using SQL Server 2012. There is the new TRY_PARSE and TRY_CONVERT where you can specifiy the target culture. You will get back a valid date/time or NULL if the cast fails.

On the linked page you'll also find TRY_CONVERT where you can specify a format via style...

Check this apporach with TRY_CONVERT:

declare @dateofbirth varchar(max)='12/13/2016';
declare @dateofbirth_flag bit;

SET @dateofbirth_flag=CASE WHEN TRY_CONVERT(date,@dateofbirth,101) IS NULL THEN 0 ELSE 1 END
SELECT @dateofbirth_flag

--The result: `1` for style `101`    

SET @dateofbirth_flag=CASE WHEN TRY_CONVERT(date,@dateofbirth,102) IS NULL THEN 0 ELSE 1 END
SELECT @dateofbirth_flag

--The result: `0` for style `102`    
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download