Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

where filter col <> 0 returns error, col > 0 works, col contains no 0 Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:39 AM
Points: 14, Visits: 79
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





Post #1475127
Posted Thursday, July 18, 2013 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1475134
Posted Thursday, July 18, 2013 10:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 3,783, Visits: 8,485
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1475138
Posted Thursday, July 18, 2013 10:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
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

Post #1475143
Posted Friday, July 19, 2013 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:39 AM
Points: 14, Visits: 79
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



Post #1475323
Posted Friday, July 19, 2013 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:56 PM
Points: 2,397, Visits: 2,936
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1475324
Posted Friday, July 19, 2013 7:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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












_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1475469
Posted Friday, July 19, 2013 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:39 AM
Points: 14, Visits: 79
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
Post #1475496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse