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

 

Tags:

Development | SQL

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