1

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.

1

1 Answer 1

2

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.