Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I am new to C# and ASP.net, I am doing the validation for the Date and Time. When i enter the invalid date and time and click the OK button to save it, its throwing the following Exception. Can anyone help me out with the error and let me know how to handle this exception. For your reference attaching the error details. Thanks in advance

    Server Error in '/Application_Name' Application.

    System.Data : SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. : UPDATE [TROUBLE] SET DateDue = @DateDue,TimeDue = @TimeDue,Type = @Type,TypeId = @TypeId,ReportedTrouble = @ReportedTrouble,ReportedTroubleTypeId = @ReportedTroubleTypeId,fmTroublePriority_Id = @fmTroublePriority_Id,PageSent = @PageSent WHERE ReportNum = 1054

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: System.Data : SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. : UPDATE [TROUBLE] SET DateDue = @DateDue,TimeDue = @TimeDue,Type = @Type,TypeId = @TypeId,ReportedTrouble = @ReportedTrouble,ReportedTroubleTypeId = @ReportedTroubleTypeId,fmTroublePriority_Id = @fmTroublePriority_Id,PageSent = @PageSent WHERE ReportNum = 1054

    Source Error: 
    Line 3796:        
Line 3797:        public string UpdateTrouble(int iReportNum, string[] arrFieldName, object[] arrFieldValue) {
Line 3798:            return base.Channel.UpdateTrouble(iReportNum, arrFieldName, arrFieldValue);
Line 3799:        }
Line 3800:

    Source File: c:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\Application_Name\c06fb8fa\10e9937\App_WebReferences.4ntivsdp.4.cs    Line: 3798

    Stack Trace: 
    [FaultException`1: System.Data : SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. : UPDATE [TROUBLE] SET DateDue = @DateDue,TimeDue = @TimeDue,Type = @Type,TypeId = @TypeId,ReportedTrouble = @ReportedTrouble,ReportedTroubleTypeId = @ReportedTroubleTypeId,fmTroublePriority_Id = @fmTroublePriority_Id,PageSent = @PageSent WHERE ReportNum = 1054]
   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +10266058
   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +539
   WCFTroubleAPI.TroubleAPI.UpdateTrouble(Int32 iReportNum, String[] arrFieldName, Object[] arrFieldValue) +0
   WCFTroubleAPI.TroubleAPIClient.UpdateTrouble(Int32 iReportNum, String[] arrFieldName, Object[] arrFieldValue) in c:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\Application_Name\c06fb8fa\10e9937\App_WebReferences.4ntivsdp.4.cs:3798
   ASP.ucOrderDetails.btnEditOK_Click(Object sender, EventArgs e) in d:\PROJECT\WORK\Application_Name\UserControls\Details\Trouble\ucTroubleDetails.ascx:192
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +115
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +140
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

    Version Information: Microsoft .NET Framework Version:2.0.50727.5477; ASP.NET Version:2.0.50727.5483

    • Moved by Caillen Tuesday, June 24, 2014 8:41 AM
    Monday, June 23, 2014 9:48 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    The reason you get the exception is right there in the message. You are trying to insert a datetime value that is out of range. The valid SQL Server datetime range is between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. If you send in a datetime outside of this range, you will get the exception you mention.

    Now, there is a thing or two to know about this. First off, the .net datetime has a different range than the SQL datetime and ranges from the year 0001 up to year 9999. That means that while the .Net datetime is valid, it might break when you try to send it to SQL Server.

    In order to get around this, you have several options, depending on your preference and how your code is structured.

    One idea is to change the datatype in the SQL Server column to datetime2 which has the same range as the .Net datetime.

    Another is to catch the exception and handle it in your code after it has occured using a try/catch.

    Most likely you want to validate the data BEFORE sending it to SQL Server though, and you can check that your datetime variable is in the valid range by comparing it to the max and min values in SQL Server datetime type using System.Data.SqlTypes.SqlDateTime.MinValue and .MaxValue respectively. If your variable is outside of these, you can alert the user or whatever you need to do.

    (Yet another option is to declare your variable as a System.Data.SqlTypes.SqlDateTime and use that, but I would not go that route, but rather use the native .Net datatypes and validate the range).

    
    • Proposed as answer by OlofPetterson Monday, June 23, 2014 11:59 AM
    • Marked as answer by Just Karl Monday, June 30, 2014 4:53 AM
    Monday, June 23, 2014 11:26 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Your date is an invalid format. I don't know what you are doing to enter the data, but this seems to be more of an ASP.NET issue as to how you are entering the date and trying to save it.

    You should post to the ASP.NET forum for help.

    http://forums.asp.net/

    Monday, June 23, 2014 11:23 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    The reason you get the exception is right there in the message. You are trying to insert a datetime value that is out of range. The valid SQL Server datetime range is between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. If you send in a datetime outside of this range, you will get the exception you mention.

    Now, there is a thing or two to know about this. First off, the .net datetime has a different range than the SQL datetime and ranges from the year 0001 up to year 9999. That means that while the .Net datetime is valid, it might break when you try to send it to SQL Server.

    In order to get around this, you have several options, depending on your preference and how your code is structured.

    One idea is to change the datatype in the SQL Server column to datetime2 which has the same range as the .Net datetime.

    Another is to catch the exception and handle it in your code after it has occured using a try/catch.

    Most likely you want to validate the data BEFORE sending it to SQL Server though, and you can check that your datetime variable is in the valid range by comparing it to the max and min values in SQL Server datetime type using System.Data.SqlTypes.SqlDateTime.MinValue and .MaxValue respectively. If your variable is outside of these, you can alert the user or whatever you need to do.

    (Yet another option is to declare your variable as a System.Data.SqlTypes.SqlDateTime and use that, but I would not go that route, but rather use the native .Net datatypes and validate the range).

    
    • Proposed as answer by OlofPetterson Monday, June 23, 2014 11:59 AM
    • Marked as answer by Just Karl Monday, June 30, 2014 4:53 AM
    Monday, June 23, 2014 11:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This error occurs when the date format that the sql expects is different.Please verify: the sql date should be in the format of: M-D-Y. Surely your date format is different.

     

    Noam B.

     

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...


    • Edited by Noam B Monday, June 23, 2014 1:02 PM
    Monday, June 23, 2014 1:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the great info. I have found the issue and fixed sucessfuly.
    Wednesday, July 2, 2014 1:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    What was your fix?  I have the same issue and have done all the things above and it is still overflowing.  The weird part is that it was working until 2 weeks ago and now I am getting this date overflow issue...
    Monday, June 15, 2020 5:41 PM