locked
meaning of SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. ??? RRS feed

  • Question

  • Respected Sir/Madam;

    I'm uploading an Excel-file to a website and I always (about a 1000 times) fail to do so. It always throws an error as follows....

    SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    What is the meaning of this error. I've checked all the field in the Excel-file; about a 1000 times; like Name, Father's name Date of Birth, Admission number & so on. They all are right in the right format with the correct number of characters length.

    But why am I getting this Error. I've been haunted by this Issue since almost a week. Please help in resolving this issue as soon as possible.

    Thank YOu.

    Sunday, September 15, 2013 1:18 AM

Answers

  • What is the meaning of this error.

    Hello,

    That error means you are trying to import data into a column of SQL Server data type "datetime", but at least one data from the source exceed the allowed range of dates between year 1753 and 1999.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Papy Normand Sunday, September 15, 2013 6:56 AM
    • Marked as answer by Allen Li - MSFT Sunday, September 22, 2013 8:17 AM
    Sunday, September 15, 2013 5:56 AM

All replies

  • What is the meaning of this error.

    Hello,

    That error means you are trying to import data into a column of SQL Server data type "datetime", but at least one data from the source exceed the allowed range of dates between year 1753 and 1999.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Papy Normand Sunday, September 15, 2013 6:56 AM
    • Marked as answer by Allen Li - MSFT Sunday, September 22, 2013 8:17 AM
    Sunday, September 15, 2013 5:56 AM
  • Hello ,

    Just a little add-in to the excellent reply from Olaf.

    You should have a look at :

    http://msdn.microsoft.com/en-us/library/ms187819.aspx

    http://msdn.microsoft.com/en-us/library/ms182418.aspx

    I am feeling that you have not used the good data type for your dates.

    Maybe you are using smalldatetime which is not ANSI or ISO 8601 compliant  instead of date or datetime or datetime2.

    Please , could you provide the version of your SQL Server into you are uploading your Excel sheet ?

    For SQL Server 2000 ( no longer supported ) :

    http://msdn.microsoft.com/en-us/library/aa258277(v=sql.80).aspx

    It is possible that you have an error on the hours, minutes or seconds inside your date. It is depending of the collations/languages used by your database and your excel sheet.

    Have a nice day

    PS :

    Your error is related to SqlDateTime

    http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx

    It is possible that you have a problem with the days and months as you have not the same collation/language between your datasheet and your database/table.

    For example , if your datasheet is coming from France , you may have  01/12/2009 for the 1st December 2009 , instead , in USA , it will be understood as the 12th of January 2009, not exactly the same thing. It is why I am always using the ISO format YYYY-MM-DD which is always translated in the good way by SQL Server or any program using .Net 


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    • Edited by Papy Normand Sunday, September 15, 2013 7:41 AM Added PS
    Sunday, September 15, 2013 7:19 AM