none
Cast Error Blank Varchar to Datetime

    Question

  • I have a rather large sql statement that was generating the message "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value".  I stripped the statement down to a more basic form while still getting the message.  Heres what Im working with:

    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY CAST(AUG.RESIDDED AS DATETIME)

    RESIDDED is a varchar column.  Seems to me it should be a date column, but for now I have to leave it as is.  The Listig_RID is a primary key, and records 210912 and 213273 are very similar, both with blank('') RESIDDED fields.  If I remove the cast statement, it works fine.  If i run each record individually, it runs fine.  If i add a third (or more) record, even if that third record has a blank RESIDDED, it works fine.  If i strip out the inner join and just select and sort from the AUG table, it works fine.  Any other two records with blank RESIDDED fields work fine.  But something about those two records together is causing a problem.  I realize this is probably a data specific error and I may not be giving enough info, but any thoughts?  Perhaps theres some other relationship between those tables that I should be examining?


    • Edited by RossVth Monday, September 09, 2013 4:52 PM
    Monday, September 09, 2013 4:44 PM

Answers

  • SQL is allowed to do the query in any order it decides will be the most efficient.  Which means it is allowed to do the ORDER BY it applies the WHERE clause or after it applies the WHERE clause.  It looks like it is doing the WHERE clause first in your first query, but doing the ORDER BY clause first in the second query.

    If it does the ORDER BY first, then it will fail if ANY row has an AUG.RESIDDED value that cannot be converted to datetime.  If you are on SQL 2012, try running

    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY TRY_CONVERT(DATETIME, AUG.RESIDDED)

    If you are on an earlier release of SQL, try running

    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY CAST(CASE WHEN ISDATE(AUG.RESIDDED) = 1 THEN AUG.RESIDDED ELSE NULL END  AS DATETIME)

    That should prevent attempts to convert invalid values to datetime.

    Tom

    • Proposed as answer by Naomi NModerator Monday, September 09, 2013 6:38 PM
    • Marked as answer by RossVth Monday, September 09, 2013 11:19 PM
    Monday, September 09, 2013 6:13 PM

All replies

  • Hi,

    Please, check that your "RESIDDED" column doesn't have out-of-range dates like 0001-01-01.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, September 09, 2013 4:47 PM
  • The problems are definitely coming from the fact you're not using proper type for the Residded column. If there is a way to fix the table structure, you may want to do it rather sooner than later.

    If there are no way to fix this structure, then I suggest to do a select statement into a temp table first and then select from that temp table applying the necessary order.

    I don't think you will be able to fix the problem by any other way except for making sure to fix your whole table for bad data.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 09, 2013 5:04 PM
  • Both records have the value of '' for the RESIDDED column so it’s not some bizarre out of range date causing the problem.  I agree the better design is to have the column match the data type and I will certainly proceed with that change if/when possible.  Nevertheless, this conversion works 99% of the time, and only has a problem (that I’ve seen so far) with these two records paired together.  The default value on that column is '' and it doesn’t seem to have a problem casting '' to datetime under most cases.  The conversion works just fine on each record individually, and works just fine with both these records together when combined with other records, works fine when the join is removed, etc.  I just did another test where I switched the where clause to point to the AUG table and that worked.  So this works:
    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (AUG.Listing_RID IN (210912, 213273))
    ORDER BY CAST(AUG.RESIDDED AS DATETIME)

    But this does not:
    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY CAST(AUG.RESIDDED AS DATETIME)

    even though those Listing_RID keyes exists in both tables
    Monday, September 09, 2013 5:53 PM
  • SQL is allowed to do the query in any order it decides will be the most efficient.  Which means it is allowed to do the ORDER BY it applies the WHERE clause or after it applies the WHERE clause.  It looks like it is doing the WHERE clause first in your first query, but doing the ORDER BY clause first in the second query.

    If it does the ORDER BY first, then it will fail if ANY row has an AUG.RESIDDED value that cannot be converted to datetime.  If you are on SQL 2012, try running

    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY TRY_CONVERT(DATETIME, AUG.RESIDDED)

    If you are on an earlier release of SQL, try running

    SELECT        AUG.RESIDDED
    FROM            MLS_Amenities_FLAT_WILM_RESI AS AUG INNER JOIN
                    View_Properties AS PR ON AUG.Listing_RID = PR.Listing_RID
    WHERE        (PR.Listing_RID IN (210912, 213273))
    ORDER BY CAST(CASE WHEN ISDATE(AUG.RESIDDED) = 1 THEN AUG.RESIDDED ELSE NULL END  AS DATETIME)

    That should prevent attempts to convert invalid values to datetime.

    Tom

    • Proposed as answer by Naomi NModerator Monday, September 09, 2013 6:38 PM
    • Marked as answer by RossVth Monday, September 09, 2013 11:19 PM
    Monday, September 09, 2013 6:13 PM
  • You would not be able to answer this question and I think nobody can. The problem lies deep in the optimizer's code and how SQL Server processes a query. This problem may disappear and re-appear if new query plan is generated. That's why you can not be sure of fixing this problem for good. I think your only option is to replace all these blank values with actual default date and also examine the table for ISDATE() = 0 to see if there are other bad dates in the table and fix them.

    And start planning a long term solution of fixing the table's structure.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 09, 2013 6:16 PM
  • Casting an empty string to a datetime does not result in an out-of-range value. On my system, it returns 1900-01-01 00:00:00.000

    The problem is probably that you are assuming that the ORDER BY clause is executed after the JOIN has been performed, but that is not necessarily the case. The Optimizer is free to rearrange the order of execution, as long as the result is correct.

    Why is this relevant? Because if you have a RESIDDED that cannot convert to a datetime anywhere in the MLS_Amenities_FLAT_WILM_RESI table, then you can expect this error. Because the optimizer may choose to first select all rows from "AUG", sorted by your datetime expression, and only then eliminate any unwanted row by joining it to "PR".

    The structural solution is to change your column definition to datetime.

    The temporary solution is to clean your data, and to add a CONSTRAINT that only allows dates to be stored.

    A quick fix / kludge is to change "CAST(AUG.RESIDDED AS DATETIME)" to "CASE WHEN ISDATE(AUG.RESIDDED) = 1 THEN CAST(AUG.RESIDDED AS DATETIME) END"


    Gert-Jan

    Monday, September 09, 2013 6:31 PM