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)