locked
How to solve this error : The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. RRS feed

  • Question

  • Hi,

    I have 13 million records in the table with nvarchar(254) column ( DocDate)  with values like '2010-02-19'.

    I have created another column (DocDate1 - datetime data type) and updating with DocDate values.

    Having wrong format in the DocDate that cannot be converted to date in to DocDate1.

    I am getting an error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

    Please help me to get wrong format dates from DocDate and update remain .

    Thank you.


    • Edited by Narsa Thursday, February 13, 2014 3:10 AM
    Thursday, February 13, 2014 3:06 AM

Answers

  • This seems to be a incorrect format issue

    declare @dt varchar(15)='2010-02-19'
    
    set @dt ='02-19-2014'
    select convert(datetime,@dt)
    
    --doesn't  work
    set @dt ='19-02-2014'
    select convert(datetime,@dt)
    
    set dateformat dmy
    select convert(datetime,@dt)

    Run this in new session --to reset the default dateformat

    declare @dt varchar(15)='2010-02-19' select convert(datetime,@dt) --doesn't work set @dt ='2010-19-02' select convert(datetime,@dt) set dateformat ydm
    ----Same as above setting the dateformat could resolve the issue set @dt ='2010-19-02' select convert(datetime,@dt) set @dt ='02-19-2014' select convert(datetime,@dt) --doesn't work set @dt ='19-02-2014' select convert(datetime,@dt)

    --Same as above setting the dateformat could resolve the issue set dateformat dmy select convert(datetime,@dt)

    You also could use the ISDATE() function to find out if a the value can be converted to date.


    Satheesh
    My Blog | How to ask questions in technical forum



    • Edited by Satheesh Variath Thursday, February 13, 2014 3:44 AM
    • Marked as answer by Narsa Thursday, February 13, 2014 6:17 AM
    Thursday, February 13, 2014 3:42 AM
  • There are two possible reasons:

    1) All dates are correct, but you are running with SET DATEFORMAT dmy. In this case you can save the show with first converting to datetime2(3), if you are on SQL 2008 or later.

    2) However much more likely is that you have bogus dates like 2010-02-29 in the data. You can find the bad dates with SELECT * FROM tbl WHERE isdate(col) = 0, or use try_convert as suggested in another post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Narsa Thursday, February 13, 2014 8:15 AM
    Thursday, February 13, 2014 8:10 AM
  • This must be a data problem for date conversion. You can loop the relevant table to use Try..catch to capture the record with problem. Read

    http://technet.microsoft.com/en-us/library/ms175976.aspx


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by Narsa Thursday, February 13, 2014 6:16 AM
    Thursday, February 13, 2014 3:13 AM

  • If you have SQL Server 2012, use WHERE TRY_CONVERT (DATE, DocDate, 120) IS NULL.
    • Marked as answer by Narsa Thursday, February 13, 2014 6:17 AM
    Thursday, February 13, 2014 3:38 AM

All replies

  • This must be a data problem for date conversion. You can loop the relevant table to use Try..catch to capture the record with problem. Read

    http://technet.microsoft.com/en-us/library/ms175976.aspx


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by Narsa Thursday, February 13, 2014 6:16 AM
    Thursday, February 13, 2014 3:13 AM

  • If you have SQL Server 2012, use WHERE TRY_CONVERT (DATE, DocDate, 120) IS NULL.
    • Marked as answer by Narsa Thursday, February 13, 2014 6:17 AM
    Thursday, February 13, 2014 3:38 AM
  • This seems to be a incorrect format issue

    declare @dt varchar(15)='2010-02-19'
    
    set @dt ='02-19-2014'
    select convert(datetime,@dt)
    
    --doesn't  work
    set @dt ='19-02-2014'
    select convert(datetime,@dt)
    
    set dateformat dmy
    select convert(datetime,@dt)

    Run this in new session --to reset the default dateformat

    declare @dt varchar(15)='2010-02-19' select convert(datetime,@dt) --doesn't work set @dt ='2010-19-02' select convert(datetime,@dt) set dateformat ydm
    ----Same as above setting the dateformat could resolve the issue set @dt ='2010-19-02' select convert(datetime,@dt) set @dt ='02-19-2014' select convert(datetime,@dt) --doesn't work set @dt ='19-02-2014' select convert(datetime,@dt)

    --Same as above setting the dateformat could resolve the issue set dateformat dmy select convert(datetime,@dt)

    You also could use the ISDATE() function to find out if a the value can be converted to date.


    Satheesh
    My Blog | How to ask questions in technical forum



    • Edited by Satheesh Variath Thursday, February 13, 2014 3:44 AM
    • Marked as answer by Narsa Thursday, February 13, 2014 6:17 AM
    Thursday, February 13, 2014 3:42 AM
  • There are two possible reasons:

    1) All dates are correct, but you are running with SET DATEFORMAT dmy. In this case you can save the show with first converting to datetime2(3), if you are on SQL 2008 or later.

    2) However much more likely is that you have bogus dates like 2010-02-29 in the data. You can find the bad dates with SELECT * FROM tbl WHERE isdate(col) = 0, or use try_convert as suggested in another post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Narsa Thursday, February 13, 2014 8:15 AM
    Thursday, February 13, 2014 8:10 AM
  • Hi Erland Sommarskog,

    Thank you , it is very helpful.

    Thursday, February 13, 2014 8:16 AM