April 26, 2013 at 2:42 pm
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
April 26, 2013 at 2:51 pm
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.
April 26, 2013 at 2:58 pm
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
April 26, 2013 at 3:05 pm
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.
April 26, 2013 at 3:11 pm
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
April 26, 2013 at 3:15 pm
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
April 26, 2013 at 4:29 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 29, 2013 at 1:44 pm
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
April 29, 2013 at 8:47 pm
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