Find row with conversion error row in sql

Question

I need to find the error causing row from the table in sql, for example:

We have invoice table, if I need to convert the Invoice date column it results an error due to special character present at Row ID 4:

Row ID   INVOICE DATE   AMOUNT
1        05/22/2015     25
2        05/27/2015     85
3        05/17/2015     15
4        ,              28

My question is how to find the Row ID if invoice table has billion of data. Is there any way to get the Row ID?

Answer 1

Use ISDATE function

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0

select * 
from yourtable
where ISDATE([INVOICE DATE]) = 0

In SQL Server 2012+ you can use TRY_CONVERT function to do the conversion.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

If the string is valid date then it will be converted to date else string will be converted to NULL

Update: Based on your comments

Sample Data

CREATE TABLE test_tab
    ([Row ID] int, [INVOICE DATE] varchar(10), [AMOUNT] int)
;

INSERT INTO test_tab
    ([Row ID], [INVOICE DATE], [AMOUNT])
VALUES
    (1, '05/22/2015', 25),
    (2, '05/27/2015', 85),
    (3, '05/17/2015', 15),
    (4, ',', 28)
;

Query

SELECT [Row ID],
        Try_convert(date, [INVOICE DATE]) AS [INVOICE DATE],
        amount,
        CASE
            WHEN Try_convert(date, [INVOICE DATE]) IS NULL THEN Concat('Row ID ', [Row ID], ' has some conversion issue')
            ELSE 'Valid Date'
        END AS comments
FROM   test_tab 

Result:

╔════════╦══════════════╦════════╦════════════════════════════════════╗
║ Row ID ║ INVOICE DATE ║ amount ║              comments              ║
╠════════╬══════════════╬════════╬════════════════════════════════════╣
║      1 ║ 2015-05-22   ║     25 ║ Valid Date                         ║
║      2 ║ 2015-05-27   ║     85 ║ Valid Date                         ║
║      3 ║ 2015-05-17   ║     15 ║ Valid Date                         ║
║      4 ║ NULL         ║     28 ║ Row ID 4 has some conversion issue ║
╚════════╩══════════════╩════════╩════════════════════════════════════╝

Answer 2

I recently encountered a similar problem when casting strings with numeric values to a numeric data type. ISNUMERIC() wouldn't do, since it returns 1 for strings like '-' and '-,', while a CAST fails on such values.

The technique shared above wouldn't work, since the column contained many Null values and empty strings, and these should be casted to Null

You can use PARSE to track down the errors in such cases, since the error message for PARSE includes the value that caused the error. Then you can search for that value using a WHERE clause.