Use T-SQL ISDATE() function with care

I have used the T-SQL function ISDATE() function in many places in my client projects. I came across an interesting behavior that caught me off guard. Try the following T-SQL:

declare @seedDate varchar(20) = '9966'
if isdate(@seedDate) = 1
 Print 'Good Date ' + @seedDate
else
 Print 'Invalid Date ' + @seedDate

declare @myDate Date
select @myDate = @seedDate
print @myDate

Running this bit of code produces this result:

Good Date 9966
9966-01-01

Notice that the "9966" value is implicitly converts to Jan. 1, 1996. This implicit conversion produces a valid date and ISDATE returns 1.

Here is a link to the ISDATE documentation from Microsoft.

http://msdn.microsoft.com/en-us/library/ms187347(SQL.105).aspx

Watch out for this situation. Validating with ISDATE without checking the length of the date leaves a hole in the validation.