datetime comparison behaving oddly

  • I think I'm about to fill a gap in my knowledge base, because I've been banging my head against this all day and I'm baffled. I'm going to provide as simplified a version as possible for illustration purposes.

    OBS is a table that contains a variety of observations for hospital patients. The field in which the observation is stored is a varchar(2000) called OBSVALUE. This can contain just about anything - in many cases, it contains a date. These are the values I'm interested in.

    Now for some code:

    WITH cte_OBS AS (

    SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE

    FROM OBS

    WHERE ISDATE(OBSVALUE) = 1

    )

    SELECT OBSVALUE

    FROM cte_OBS

    WHERE OBSVALUE < GETDATE()

    If I execute the contents of the CTE, I get 66350 rows. If I execute the entire thing except for the WHERE clause, I get 66350 rows. If I execute everything including the WHERE clause, I get:

    Msg 241, Level 16, State 1, Line 1

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

    The same thing happens if I replace GETDATE() with CAST('4/1/2013' as datetime) or CONVERT(datetime,'4/1/2013'). I'm totally stumped. Can anyone enlighten me?

    thanks!

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Just for fun... do this...

    ;WITH cte_OBS AS (

    SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE

    FROM OBS

    WHERE ISDATE(OBSVALUE) = 1

    )

    SELECT OBSVALUE

    into dbo.temperic_OBSVals

    FROM cte_OBS

    once the table is created, ALT+F1 to see what the data type is for OBSVALUE column.

  • Eric Nordlander (4/26/2013)


    once the table is created, ALT+F1 to see what the data type is for OBSVALUE column.

    It's a datetime. And of course this gives no errors...

    SELECT OBSVALUE

    FROM temperic_OBSVals

    WHERE OBSVALUE < GETDATE()

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • 1. what are the min and max values in the table? (the ranges for the data types is why it could be a concern--> http://msdn.microsoft.com/en-us/library/ms187347.aspx)

    2. try assigning getdate() to a variable of datetime and compare to that instead of the function.

  • Eric Nordlander (4/26/2013)


    1. what are the min and max values in the table? (the ranges for the data types is why it could be a concern--> http://msdn.microsoft.com/en-us/library/ms187347.aspx)

    2. try assigning getdate() to a variable of datetime and compare to that instead of the function.

    1. 4/14/2001 and 12/31/2999. Doesn't seem like that should be a problem, if I'm reading the article correctly.

    2. Same error.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • I did some checking on a small sample of data (included with a solution below), and I discovered that the OBSValue works just fine as a datetime value in the SELECT clause of the main query, but not in the WHERE clause. Weird. My guess is that the query optimizer is trying to make the logic more efficient by moving the filter earlier in the execution, but inadvertently moved it to a point before it was "cast" into a datetime format. Just a WAG on my part.

    One solution to the problem (bug?) is to move the comparison up into the CTE and just cast it in the WHERE clause too.

    with

    OBS as

    ( select '1/1/2012' as obsvalue

    union all select 'test data'

    union all select 'Mar 3 2010'

    union all select 'more Apr 1 2000'

    union all select '7/24/2013'),

    cte_OBS as

    (select

    OBSValue = cast(obsvalue as datetime)

    from OBS where isdate(obsvalue) = 1 and cast(obsvalue as datetime) < getdate())

    select

    obsvalue,

    datediff(day, OBSValue, getdate())

    from

    cte_OBS

  • I'd use a CASE expression so you can guarantee the order of execution; you don't need a CTE for that.

    SELECT OBSVALUE

    FROM OBS

    WHERE 1 = CASE

    WHEN ISDATE(OBSVALUE) = 0 THEN 0

    WHEN OBSVALUE < GETDATE() THEN 1

    ELSE 0 END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (4/26/2013)


    I'd use a CASE expression so you can guarantee the order of execution; you don't need a CTE for that.

    SELECT OBSVALUE

    FROM OBS

    WHERE 1 = CASE

    WHEN ISDATE(OBSVALUE) = 0 THEN 0

    WHEN OBSVALUE < GETDATE() THEN 1

    ELSE 0 END

    This worked perfectly, thank you!

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • The couse of this trouble is "resolving views" or "opening derived tables" behaviour which was once fixed in SQL2000 SP3 and sadly reintroduced in SQL2000 SP4.

    It stays with us since then.

    The query WITH cte_OBS AS (

    SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE

    FROM OBS

    WHERE ISDATE(OBSVALUE) = 1

    )

    SELECT OBSVALUE

    FROM cte_OBS

    WHERE OBSVALUE < GETDATE()

    is the same for optimiser as

    SELECT OBSVALUE

    FROM (

    SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE

    FROM OBS

    WHERE ISDATE(OBSVALUE) = 1

    ) cte_OBS

    WHERE OBSVALUE < GETDATE()

    and is translated while being compiled into this:

    SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE

    FROM OBS

    WHERE ISDATE(OBSVALUE) = 1 and OBSVALUE < GETDATE()

    Having 2 conditions in the WHERE clause optimiser starts from the one it'decided is more selective.

    ISDATE(OBSVALUE) = 1 will always require full index scan, and OBSVALUE < GETDATE() might be able to use an index.

    So optimiser starts with checking OBSVALUE < GETDATE() and runs into an error when implicit conversion of OBSVALUE to datetime fails. it never gets to ISDATE check.

    I tried in the past to fix such issues by putting CTE/DT code into views but it does not help, unless you have a GROUP BY clause in it.

    _____________
    Code for TallyGenerator

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply