Find row with conversion error row in sql

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.

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 ║
╚════════╩══════════════╩════════╩════════════════════════════════════╝