Wednesday, September 9, 2009

ISDATE() function returns 0 for valid DATETIMEOFFSET values?

Quite confusing but true. I was working on trying to validate a specific value if it is indeed a valid DATETIMEOFFSET data type using the ISDATE() function until I saw this forum post

"Adam,

The problem is that ISDATE returns 0 for valid DATETIMEOFFSET values.
"

I couldn't believe it since Books Online until I tested it out myself

IF ISDATE('2009-09-08 10:19:41.177 -05:00') = 1
PRINT 'VALID'
ELSE
PRINT
'INVALID'


This returns INVALID when, in fact, it is a valid datetime value with time zone awareness. I even tried the values provided by Books Online and still gives me invalid results. I have yet to wait for a response from the SQL Server product group regarding this but at least it gives us an opportunity to dig deper
Google