CODEWITHSUNDEEP
sqlsql-server
Sampath
Sampath

Reputation: 65870

The conversion of a nvarchar data type to a datetime, data type resulted in an out-of-range value

Runtime exception:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Code:

INSERT INTO [Migrated].[dbo].[MyTables] (LegacyId,DeedDate)
    SELECT DISTINCT 
        a.[IPLID], CONVERT(nvarchar(255), a.[Deeddate], 127) 
    FROM 
        [Legacy].[dbo].[MyTables2] as a

Hope I have done it correctly. But why it gives above error ?

I'm using SQL Server 2014 express.

Note : Can you tell me how to find out defects on MyTables2 Deeddate ? I mean different date formats and etc.Or Replace such dates with NULL.

Upvotes: 2

Views: 10518

Answers (2)

Sampath
Sampath

Reputation: 65870

I have found out the defect dates by using ISDATE function and removed it.After that it works.Cheers :)

This works then :

INSERT INTO [Migrated].[dbo].[MyTables] (LegacyId,DeedDate)
    SELECT DISTINCT 
        a.[IPLID], CONVERT(nvarchar(255), a.[Deeddate], 127) 
    FROM 
        [Legacy].[dbo].[MyTables2] as a

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

In SQL Server 2012+, you can use TRY_CONVERT() which will return NULL rather than an error:

INSERT INTO [Migrated].[dbo].[MyTables](LegacyId, DeedDate)
    SELECT DISTINCT a.[IPLID], TRY_CONVERT(nvarchar(255), a.[Deeddate], 127)
    FROM [Legacy].[dbo].[MyTables2] a;

You can also use this to determine where the format is not correct:

SELECT a.*
FROM (SELECT a.[IPLID], a.[Deeddate],
             TRY_CONVERT(nvarchar(255), a.[Deeddate], 127) as datestr
      FROM [Legacy].[dbo].[MyTables2] a
     ) a
WHERE datestr IS NULL;

Upvotes: 1

Related Questions