MS SQL: Should ISDATE() Return "1" when Cannot Cast as Date?

Question

everyone,

On a SQL Server instance running 2012 SP3, the following code returns "1":

SELECT ISDATE('january,25,1999')

However, the following fails the conversion:

SELECT CAST('january,25,1999' AS DATE)

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Am I misunderstanding what ISDATE() ought to return? Why does ISDATE() return a value of "1" for the string? How ought we determine, from SQL Server, whether strings such as "january,25,1999" can be casted as dates?

Thanks!

Answer 1

Forget ISDATE()! You are using SQL Server 2012:

SELECT TRY_CONVERT(date, 'january,25,1999')

This will return NULL if the date cannot be converted -- which I don't think this can be. You can specify a third argument for the particular format you want to convert.

I will note something that in SQL Server 2014:

SELECT TRY_CONVERT(date, 'january,25,1999')

returns an error.

SELECT TRY_CONVERT(datetime, 'january,25,1999')

succeeds.

I have no idea why. But for what you want:

SELECT CONVERT(DATE, TRY_CONVERT(datetime, 'january,25,1999'))