sql server - How to find conversion error row in sql -

i need find error causing row table in sql eg :

we have invoice table , if need convert invoice date column results error due special character present @ 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 how find row id if invoice table has billion of data. there way row id ? please

use isdate function

returns 1 if expression valid date, time, or datetime value; otherwise, 0

select *  yourtable isdate([invoice date]) = 0 

in sql server 2012+ can use try_convert function conversion.

returns value cast specified data type if cast succeeds; otherwise, returns null.

if string valid date converted date else string converted null

update: based on comments

sample data

create table test_tab     ([row id] int, [invoice date] varchar(10), [amount] int) ;  insert 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]) [invoice date],         amount,         case             when try_convert(date, [invoice date]) null concat('row id ', [row id], ' has conversion issue')             else 'valid date'         end comments   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 conversion issue ║ ╚════════╩══════════════╩════════╩════════════════════════════════════╝