Can't find which string is causing the conversion error
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 am getting this error:
Conversion failed when converting datetime from character string.
The status bar says "2390 lines". I move on to lines 2390, 2391 and 2392. There is nothing wrong with the data. I even try to convert these specific strings and it works. How do I know which line (s) is causing the conversion error?
+2
a source to share
2 answers
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
+6
a source to share
There is no primary key in this table. In its absence, the table viewer and the query cannot be guaranteed to run in the same order, so 2390 means different rows. Once the primary key has been created, the row number displayed when the query is run will match the row number in the table view.
+1
a source to share