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

2
votes

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
  • MyTables --> DeedDate datetime Allow Null
  • MyTables2 --> Deeddate nvarchar(255) Allow Null

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.

2
sqlsql-server
you are trying to insert nvarchar into a datetime column. the error message is clear. – Vamsi Prabhala
You have a date before 1.1.1753 (or after 31.12.9999)? – James Z
@vkp That's why I have converted it.Am I wrong ? – Sampath
Try using smaller and smaller sets of MyTables2 to determine exactly which value(s) are causing the issue. – Steven
you are still converting it to nvarchar. – Vamsi Prabhala

2 Answers

3
votes

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
1
votes

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;