SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


where filter col <> 0 returns error, col > 0 works, col contains no 0


where filter col <> 0 returns error, col > 0 works, col contains no 0

Author
Message
Ralf
Ralf
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 109
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
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5000 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16615 Visits: 19098
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
dan-572483
dan-572483
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 1999
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
Ralf
Ralf
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 109
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
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4404 Visits: 3670
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’! **
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5000 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Ralf
Ralf
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 109
Eugene, I believe you Smile

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search