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

  • 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

  • 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[/url]

  • 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
  • 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

  • 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

  • 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’! **
  • 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[/url]

  • 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