Sql-server – How to get the row causing a conversion failure in SQL Server

errorssql serversql-server-2012

I have an update statement that is failing with this,

Msg 242, Level 16, State 3, Line 63
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

I understand the error. I want to know which row I am trying to convert is failing. If I have 1200 rows, how can I know which one is causing the problem without looking through it by hand? Is there a way to get more detailed ouput?

I ended up doing the answer to this one,

https://stackoverflow.com/questions/1094483/sql-server-find-out-what-row-caused-the-tsql-to-fail-ssis

But it does not seem right.

Best Answer

This will help you figure out what value is causing your error. The example below shows a table with a NULL, a word, and a time stamp. You can see by running a case when statement that analyzes the value to essentially see if it's a date or could be a date and prints the value that is not a date. The CTE wrap up gives us a clean cut view into what specific values are not dates, so you can edit that value.

As Erik said, you can do a try_convert to get the update to take. The approach below will show you which value is not converting.

DECLARE @table TABLE (test VARCHAR(255))

INSERT  INTO @table
        (test)
VALUES
        ('cat'  -- test - varchar(255)
            )

INSERT  INTO @table
        (test)
VALUES
        (NULL)

INSERT  INTO @table
        (test)
VALUES
        ('2017-03-04');
WITH    results
            AS (
                SELECT
                    ISNULL((CASE    WHEN ISDATE(test) = 0 THEN test
                                    ELSE 'not error'
                            END) , 'not error') AS test
                FROM
                    @table
                )
    SELECT
        *
    FROM
        results
    WHERE
        test != 'not error'
Related Question