--this date MAY or MAY NOT be valid depending on the DATEFORMAT setting

SELECT ISDATE('29-12-2013')

--this is never valid (out-of-range)

SELECT ISDATE('32-12-2013')

--this should always be valid in all formats

SELECT ISDATE('2013-01-08 15:44:12.208')

--this is a valid UMC date but ISDATE = 0

SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')

DECLARE @testdate VARCHAR(20)

SET @testdate = '29-12-2013' -- this is valid in DMY format

SET @testdate = '32-12-2013' -- this is never valid (out-of-range)

SET @testdate = '2013-01-08 15:44:12.208' -- this is valid

SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date

--for these testdates using the script below,

--#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT

SELECT

@testdate

,IsDateValid = CASE WHEN @testdate IS NULL THEN 0

WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0

WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0

ELSE 1

END

--this procedure returns the proper results

EXEC dbo.IsValidDate '29-12-2013','DMY'

EXEC dbo.IsValidDate '32-12-2013'

EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'

EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'