Siddharth Bhardwaj Siddharth Bhardwaj - 3 months ago 14
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

mm/dd/yyyy
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 '%[:]%')
begin
set @dateofbirth_flag=1;
end
else
begin
set @dateofbirth_flag=0;
end

Answer

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`