Identify the row / value that caused a conversion / out-of-range error?

  • pesche_h

    SSC-Addicted

    Points: 407

    Hello board

    Often, when I try to import old date from a table, where the date is stored as varchar to a table, where the same field should be smalldatetime, I get conversion or out-of-range errors. Is there any easy way of identifying which row or value caused the error? I only get error messages such as

    The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

    That may help me finding out which column caused the error, but if the table consists of thousands or millions of rows, the error message does not help me at all finding out which value was the cause. One way of identifying it is to use TOP ... and stepwise extend the range until you got your error message. But there must be a simpler way, a setting, a special query ...

    Thank you very much for any hints!

    Hanspeter

  • pesche_h

    SSC-Addicted

    Points: 407

    I've already found kind of a solution for the out-of-range error, that will produce NULL instead of an error (you just have to go through all NULLS instead of the entire table), with the help of the following article:

    http://www.sommarskog.se/error-handling-I.html#anatomy

    -- out-of-range

    ---------------

    -- Default:

    SET XACT_ABORT OFF

    SET ANSI_WARNINGS ON

    SET ARITHABORT ON

    SELECT CONVERT(datetime, '20031234') -- This causes an overflow

    GO

    -- Adjusted settings:

    SET XACT_ABORT ON -- very most of the statement-terminating errors instead become batch-aborting errors

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    SELECT CONVERT(datetime, '20031234') -- NULL instead of error-message

    Unfortunately, this doesn't work for conversion errors 🙁

  • Nabha

    SSCrazy Eights

    Points: 8534

    How is your date stored in char data type, I mean the format? what is the conversion that you are doing?

    ---------------------------------------------------------------------------------

  • pesche_h

    SSC-Addicted

    Points: 407

    e.g. the following simple examples:

    SELECT CONVERT(datetime, '20031234') -- This causes an overflow

    SELECT CONVERT(datetime, '2003123') -- This causes a conversion error

    In more practical terms, I was trying to import a table containing a field of type varchar(8). This field should be converted to smalldatetime. The old table not only contained valid dates, but also

    '00000000'

    ' 0'

    --> conversion errors

    and

    'Feb 29 2'

    --> out-of-range error

    I found the invalid values manually, and am looking for a way to make SQL-Server tell me which values are invalid.

    Thank you very much and best regards

    Hanspeter

  • Nabha

    SSCrazy Eights

    Points: 8534

    Checkout ISDate,

    http://msdn.microsoft.com/en-us/library/ms187347(SQL.90).aspx

    ---------------------------------------------------------------------------------

  • Madhivanan-208264

    SSCertifiable

    Points: 7516

    Note that isdate() is not fully reliable

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx


    Madhivanan

    Failing to plan is Planning to fail

  • pesche_h

    SSC-Addicted

    Points: 407

    Thank you very much for the hints, that works nicely, so far!

    However, is there also a solution for other conversions than date-conversions? (e.g. overflow when converting to smallint, conversion error when converting varchar to int (when it contains other chars than numbers) etc.)?

    Basically, is there any way to make SQL-Server tell you which value caused the error?

    Thank you

    Hanspeter

  • Madhivanan-208264

    SSCertifiable

    Points: 7516

    A quick way should be

    SELECT date_col from your_table

    where right(date_col,2)>31 or substring(date_col,5,2)>13


    Madhivanan

    Failing to plan is Planning to fail

  • pesche_h

    SSC-Addicted

    Points: 407

    That's a good idea, but it would still not recognize cases such as

    '20020229'

    '0'

    '00000000'

    Thank you anyway 🙂

  • Nabha

    SSCrazy Eights

    Points: 8534

    pesche_h (12/8/2009)


    Thank you very much for the hints, that works nicely, so far!

    However, is there also a solution for other conversions than date-conversions? (e.g. overflow when converting to smallint, conversion error when converting varchar to int (when it contains other chars than numbers) etc.)?

    ISNUMBER is one thing you may have to look at and to check if the number would fit into smallint you can do a case statement and check the range and return whatever you want. (Not sure if there is any other elegant way

    Basically, is there any way to make SQL-Server tell you which value caused the error?

    Thank you

    I think its all about handling it graciously and i dont think there is any straightforward way.A combination of several things could get what you want to know ( I could be wrong) and see Try catch block as well

    ---------------------------------------------------------------------------------

  • Garadin

    One Orange Chip

    Points: 29613

    Dear Tally Table. How I love thee, let me count the ways...

    SELECT *

    FROM YourTable

    WHERE pseudodatecolumn NOT IN (

    select CAST(YEAR(dateadd(d,n,'19910101 00:00:00')) as char(4)) +

    RIGHT('0' + CAST(MONTH(dateadd(d,n,'19910101 00:00:00')) as varchar(2)),2) +

    RIGHT('0' + CAST(DAY(dateadd(d,n,'19910101 00:00:00')) as varchar(2)),2)

    from tally

    WHERE N < 10000 --(This would go from 1991 - 2018, adjust your date range accordingly)

    )

    See the article in my signature for what a tally table is. I'll post a cte version tomorrow at work (or someone else can).

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply