Can't find which row is causing conversion error

primary-key, sql-server, type-conversion

Solution

Try:

SELECT * FROM [dbo].[Accounts1] WHERE ISDATE(ExpiryDate) = 0

Here's my test code:

CREATE TABLE #t( 
    [ExpiryDate] [nvarchar](50) NULL 
) 

insert into #t (ExpiryDate)
select '1/1/2010'

insert into #t (ExpiryDate)
select 'foo'

insert into #t (ExpiryDate)
select '2/1/2010'

select * from #t where ISDATE(ExpiryDate) = 0
-- returns 1 row

drop table #t

Problem

I have the following table: ``` CREATE TABLE [dbo].[Accounts1]( [AccountId] [nvarchar](50) NULL, [ExpiryDate] [nvarchar](50) NULL ) ``` I am trying to convert nvarchar to datetime using this query: ``` select convert(datetime, expirydate) from accounts ``` I get this error: ``` Conversion failed when converting datetime from character string. ``` The status bar says "2390 rows". I go to rows 2390, 2391 and 2392. There is nothing wrong with the data there. I even try to convert those particular rows and it works. How can I find out which row(s) is causing the conversion error?

Original source