IsDate Function in SQL evaluates invalid dates as valid
Solution 1
I do a lot of data conversion work and here is a function that I created and use it practically everyday to weed out the bad dates:
CREATE FUNCTION dbo.fnCheckDate
(@InDate nvarchar(50))
RETURNS DATETIME
AS
BEGIN
declare @Return DATETIME
select @return = CASE WHEN ISDATE(@InDate) = 1
THEN CASE WHEN CAST(@InDate as DATETIME) BETWEEN '1/1/1901 12:00:00 AM' AND '6/6/2079 12:00:00 AM'
THEN @InDate
ELSE null
END
ELSE null
END
return @return
END
GO
Results:
SELECT dbo.fnCheckDate('07/001/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('2012-07-002') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('007/002/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('00/002/2012') --> Returns Null
SELECT dbo.fnCheckDate('006/031/2012') --> Returns Null
SELECT dbo.fnCheckDate('') --> Returns Null
Solution 2
Try setting the dateformat
first - that worked for me when I was seeing exceptions.
set dateformat dmy
select IsDate(<column>)
from Table
Solution 3
maybe just check dt's LEN? however, it can not handle cases when the len is valid. maybe input validation should happen in the frontend?
![ssokol91](https://i.stack.imgur.com/sMdn8.jpg?s=256&g=1)
ssokol91
Updated on July 05, 2022Comments
-
ssokol91 about 2 years
I am running a SQL Statement against imported data from Excel Files. In this SQL I am checking if the users have entered dates properly by using IsDate function. Since this is a raw data that hasn't been converted yet, all dates are stored in a varchar data type field.
In some circumstances IsDate returns 1 (valid date) when there is clearly an incorrect date format entered by the user.
For Example:
07/001/2012 2012-07-002 007/002/2012
Any Suggestions on how to handle this problem?
SELECT * FROM tblImport WHERE (ISDATE(dt) = 0 AND (dt is not null AND dt <> ''))
Thanks!
p.s. Smacking users' did not help.