Michael Valentine Jones (9/30/2011)
Here is a fairly simple check that will work with any setting of DATEFIRST:
select
a.dt,
IsValidDateTime =
case
-- Check that date string is in correct format, mm/dd/yyyy hh:mm:ss
when dt not like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
then 0
-- Reformat and test date in universal format of yyyymmdd hh:mm:ss
when isdate(substring(a.dt,7,4)+substring(a.dt,1,2)+substring(a.dt,4,2)+substring(a.dt,11,9)) <> 1
then 0
else 1
end
from
( -- Test Data
select dt = '08/31/2001 14:34:56'union all
select dt = '08/31/2001 34:34:56'union all
select dt = '08/31/2001 14:64:56'union all
select dt = '08/31/2001 14:34:66'union all
select dt = '08/31/2001 25:34:56'union all
select dt = '08/31/2001 14:34:56'union all
select dt = '13/01/2001 14:34:56'union all
select dt = '0a/31/2001 14:34:56'union all
select dt = '08/32/2001 14:34:56'union all
select dt = '21/31/2001 14:34:56'
) a
Results:
dt IsValidDateTime
------------------- ---------------
08/31/2001 14:34:56 1
08/31/2001 34:34:56 0
08/31/2001 14:64:56 0
08/31/2001 14:34:66 0
08/31/2001 25:34:56 0
08/31/2001 14:34:56 1
13/01/2001 14:34:56 0
0a/31/2001 14:34:56 0
08/32/2001 14:34:56 0
21/31/2001 14:34:56 0
(10 row(s) affected)
That would allow 30th February as a valid date. . . In fact, it'd allow 39th February 😉