Use T-SQL ISDATE() function with care

by marc walgren14. January 2013 07:18

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
 Print 'Invalid Date ' + @seedDate

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


Running this bit of code produces this result:

Good Date 9966

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.

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



Development | SQL

blog comments powered by Disqus

Contact Us  Consulting  Web Development  Data Collection  Flexible Web Lists  Clarion Products  Downloads  How To Order  Site Map  Store  Home
Copyright (c) 1989-2013 Mitten Software Inc., All rights reserved.

Month List