problem using ISDATE in a Function

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

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)

Cadavre (9/30/2011)

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 Wink