Can't find which row is causing conversion error

Question

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?

Answer 1

This table does not have a primary key. In its absence the table viewer and the query cannot be guaranteed to operate in the same order thus 2390 means different rows. As soon as a primary key is created, the row number shown when running the query will match the row number on the table view.

Answer 2

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