sqlsql-servercastingtype-conversion

SQL Server - Value passes ISDATE() but fails to CAST as DATE or DATETIME


I have a varchar column in my database table, on the row I would like to return it is populated as '2018-12-26T00:00:00.000' (quotes mine, not included in actual value). When I try to query for this value whenever it is a valid date, e.g.

SELECT
    myValue
FROM 
    myTable
WHERE
    ISDATE(myValue) = 1

it returns properly. However, I need this value to be converted to DATE. When I try something like this:

SELECT
    CAST(myValue AS DATE) AS myValueFormatted
FROM 
    myTable
WHERE
    ISDATE(myValue) = 1

I get an error

Conversion failed when converting date and/or time from character string

Is there any other way I can convert this varchar value to Date?

UPDATE: I've noticed through trying some different things, the query seems to be fine with me using the value as a date for anything (DATEDIFF, CONVERT back to string, etc.) in the select portion, but trying to do anything with it in the WHERE clause causes the error. To ensure nothing else is interfering, I created a temp table with only 1 row with the data value above, and running the query just against that one value gives the error

UPDATE 2: Ok, I have no idea why this fixes it, but this is what I found. When I run

SELECT
    myValue
FROM
    myTable
WHERE
    TRY_CONVERT(DATE, myValue) IS NOT NULL

it returns EXACTLY the same values as

SELECT
    myValue
FROM 
    myTable
WHERE
    ISDATE(myValue) = 1

However, when I then add AND CAST(myValue AS DATE) < GETDATE() to each WHERE clause, only the first one works. I understand why TRY_CONVERT is safer to use, I'm still not sure why it works over GETDATE()


Solution

  • I can't reproduce your error...

    declare @dt varchar(256) = '2018-12-26T00:00:00.000' select cast(@dt as date)

    So, there must be another rogue value in there that can't be converted.

    To identify what value is causing the issue on versions < 2012, run this:

    SELECT
    myValue
    FROM myTable
    WHERE
    ISDATE(myValue) = 0
    

    Note, ISDATE is deterministic only if you use it with the CONVERT function, if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.

    For 2012 onward, use TRY_CONVERT

    SELECT
    *
    FROM myTable
    WHERE
    TRY_CONVERT(date, myValue) IS NULL