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

sqlsql server

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.

Best Answer

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
Related Topic