ISDATE() function returns 0 for valid DATETIMEOFFSET values?


Post to Twitter Post to Facebook Post to StumbleUpon

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