July 18, 2013 at 10:13 am
Hi,
I can't explain the following behaviour:
I join 2 tables and depending on the where filter I get an error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
table A is a heap, table B has a clustered index
B.Date is CHAR(8)
this does not work:
SELECT CONVERT(DATETIME,B.Date)
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID <> 0
this works:
SELECT CONVERT(DATETIME,B.Date)
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID > 0
I don't have a 0 in Table A.
hmm...why do I get an error message with WHERE a.ID <> 0 ,altough I don't have 0 in a.ID
thank you for your help!
cheers
ralf
July 18, 2013 at 10:28 am
If you got "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error that mean that there are some values in table you select from, cannot be converted into DATETIME.
Now, you may say that this values are supposed to be filtered out by WHERE clause.
Yep, however I have seen casess where validation, conversion and division by zero erros are raised due to the SQL optimiser desides to perform relevant actions before applying WHERE filters and/or JOIN's.
Last time I've seen this behaviour it was SQL 2005 and we had very (very) large tables...
Microsoft SQL Server consultant confirmed that it is possible after speaking to MS devs working on SQL Server...
However, you may find that something more trivial causes your problem...
July 18, 2013 at 10:39 am
What do you get when you do this?
SELECT B.Date
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID < 0
You need to find out if there are any invalid dates in your B.Date column. You might also want to try the following
SELECT B.Date
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE ISDATE(B.Date) = 0
July 18, 2013 at 10:42 am
I'd guess you have at least one record with an ID that is a negative number, and has a value in DATE that cannot be converted to a DATETIME value.
Try
SELECT * FROM TABLE WHERE ID < 0
July 19, 2013 at 2:34 am
yes, it seems that the conversation to datetime is performed before the WHERE filter.
maybe because of parallel processing and the large size of the table A (~300 MB)....this is new to me
the thing is, because of the error I'm not able to check the execution plan....or is there a trick to show the execution plan anyway?
thank's a lot
Ralf
July 19, 2013 at 2:39 am
You can remove the CONVERT part from the SELECT to get the (estimated) query plan. But that doesn't remove the incorrect value.
You really should fix this incorrect value(s) first.
July 19, 2013 at 7:21 am
Ralf (7/19/2013)
yes, it seems that the conversation to datetime is performed before the WHERE filter.maybe because of parallel processing and the large size of the table A (~300 MB)....this is new to me
the thing is, because of the error I'm not able to check the execution plan....or is there a trick to show the execution plan anyway?
thank's a lot
Ralf
Not many people believe me too. But it does happen (at least in SQL2005) and it was confiremd by MS that is't possible.
Now. How to overcome this?
We found few ways to deal with this sort of behaviour, it depends on what you are really doing.
If for example query like that: SELECT Col1/Col2 FROM Table WHERE Col2 != 0
causes the "Devision by zero" error, the easierst way to fix it would be:
SELECT Col1/NULLIF(Col2, 0) FROM Table WHERE Col2 != 0
Cases with CONVERSION or filtering via JOIN's are a bit more complecated.
So, basically you need to SELECT INTO valid records into temp table, then perform your conversion.
My understanding is that your query could potentially try to convert non-datetime value if you wouldn't have a WHERE clause or JOIN.
So, try doing that:
SELECT B.Date
INTO #t
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID <> 0
SELECT CONVERT(DATETIME,Date)
FROM #t
July 19, 2013 at 7:57 am
Eugene, I believe you 🙂
altough it is contradict to what I've learned about logical query processing.
and yes, the use of a temporary table is probably the only solution to this.
thank you very much.
Ralf
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply