Varchar-to-Datetime conversion error - fine in SELECT but breaks in WHERE

Question

I have a simple CTE that pulls dates from a table - the dates (ApptDateTime) are stored in YYYY-MM-DDZHH:mm:SS format. Some columns have NULL values, so I want to ignore them, along with anything that isn't really a date.

If I do:

;WITH cte
AS
(SELECT
        urn,
        CONVERT(DATETIME, ApptDateTime) AS ApptDateTime

    FROM data
    WHERE ISDATE(ApptDateTime) = 1  
)

SELECT
    *
FROM cte
ORDER BY ApptDateTime

... this works fine; I get a list of records back, ordered by date. However, if I want to see only dates that are before today and introduce a WHERE:

;WITH cte
AS
(SELECT
        urn,
        CONVERT(DATETIME, ApptDateTime) AS ApptDateTime

    FROM data
    WHERE ISDATE(ApptDateTime) = 1  
)

SELECT
    *
FROM cte
WHERE ApptDateTime < GETDATE()
ORDER BY ApptDateTime

I get the error

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

I can't see any dodgy non-date, non-NULL values in the table, and even if there were I'd expect anything that wasn't able to be CONVERTed to cause an issue as part of the SELECT in the CTE. However, it only throws that error when I'm trying to compare the datetime. This seems so simple that I'm certain I'm missing something blindingly obvious - but I can't see it.

EDIT: If I send the results to a temp table, and then do the comparison on that ... it works fine!

SELECT
    urn,
    convert(datetime,ApptDateTime) as ApptDateTime
    into #temp FROM data
    WHERE ISDATE(ApptDateTime) = 1  


SELECT
    *
FROM #temp
WHERE ApptDateTime < getdate()
ORDER BY ApptDateTime

DROP TABLE #temp

maybe the nulls are a problem, have you tried something like where isnull(ApptDateTime, '20990101') < getdate() ? — GuidoG, Aug 12 '16 at 10:49
Same error with that included I'm afraid - I've also tried adding `AppDateTime IS NOT NULL` to the `WHERE` clause in the CTE — KenD, Aug 12 '16 at 10:50
it should not make a difference but have you tried where ApptDateTime < convert(datetime, getdate()) — GuidoG, Aug 12 '16 at 10:53
Same :( I really can't understand why: the date should have already been converted during the CTE. — KenD, Aug 12 '16 at 10:55
There's no guarantee that SQL Server won't attempt to perform the comparison *before* assessing the `ISDATE()` function call. Unfortunately, it's a [long standing issue](https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors) with little sign that they'll ever fix it. If you're on SQL Server 2012 or later, you can wrap all accesses to the column with `TRY_CONVERT` calls which then just let you deal with data or `NULL`s. — Damien_The_Unbeliever, Aug 12 '16 at 10:56
Thanks. I've managed to get it working by using a temp table (see above), but it still seems mad that it'll convert the dates for a `SELECT` but not when comparing them. — KenD, Aug 12 '16 at 11:01
@Damien_The_Unbeliever would it help to move the isdate() function to the select clause ? someghing like CASE when isdate() then convert() else '20990101' end ? — GuidoG, Aug 12 '16 at 11:01
Using `CASE` *may* work but even then it's a tad flaky. I'd usually (against usual best practice) split the query into two completely separate ones - the first populates a table variable with values that pass the `ISDATE` test, and then the second query can safely convert. — Damien_The_Unbeliever, Aug 12 '16 at 11:04

score 1 · Accepted Answer · edited May 23 '17 at 10:27

Try adding condition like this : WHERE (CASE WHEN ISDATE(ApptDateTime) = 1 THEN ApptDateTime ELSE NULL END) < GETDATE()
I faced same issue before couple of days and solved by adding CASE. Not clear about how SQL executes where condition. I also need to find actual reason for the same. If I find anything, I will post in same post.

Edit: If you see in execution plan of your actual query, it will show you ApptDateTime < GETDATE() before ISDATE(ApptDateTime) = 1 which causing the error.

Reason is WHERE clause doesn't follow short-circuit rule. Ref: Is the SQL WHERE clause short-circuit evaluated?

Yes, that works! Thanks, and thank you to everyone for the comments, I've learned something new. — KenD, Aug 12 '16 at 11:24

Varchar-to-Datetime conversion error - fine in SELECT but breaks in WHERE

1 Answers1