--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'