How can I get the row causing a conversion failure in SQL Server?

Question

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.

• Erik Reasonable Rates Darling: Since you're on 2012, you can use TRY_CONVERT msdn.microsoft.com/en-us/library/hh230993.aspx

Answer 1

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'