SQLDateTime Overflow and All DateTime fields are Valid

Question

The exception message I receive is straightforward:

SQLDateTime overflow must be between 1/1/1753 12:00:00 AM and 12/31/9999 12:59:59 PM``

This is the VB 2013 code which uses Linq to SQL:

Dim BQCust = (
    From c In FinDB.Customers
    Where c.Customer_Account = 2000223).FirstOrDefault
FinDB.SubmitChanges(ConflictMode.ContinueOnConflict)

There were no changes made in this example. I have manually checked all 6 of the datetime fields in the BQCust object of type Customer and none match the error condition. All but 1 of them are flagged to allow Nulls. The 1 that is not has a value of #5/14/2016 9:20:03 AM#. Besides, wouldn't they all have to be valid to be included in the SQL DB table that I'm retrieving them from?? So what data is causing this exception?

CREATE TABLE [dbo].[Customers](
    [RecIdent] [int] IDENTITY(1,1) NOT NULL,
    [Customer Account] [decimal](18, 0) NOT NULL,
    [Customer Name] [varchar](255) NULL,
    [Company] [varchar](255) NULL,
    [Customer Status] [varchar](255) NULL,
    [Address 1] [varchar](255) NULL,
    [Address 2] [varchar](255) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Zip Code] [char](10) NULL,
    [County] [varchar](50) NULL,
    [Date Created] [datetime] NULL,
    [Account Type] [varchar](255) NULL,
    [Sales Representative] [varchar](50) NULL,
    [Renewal Date] [datetime] NULL,
    [Account Manager] [varchar](50) NULL,
    [Acceptance Date] [datetime] NULL,
    [Voided Date] [datetime] NULL,
    [Expiration Date] [datetime] NULL,
    [LastChanged] [datetime] NOT NULL CONSTRAINT [DF_Customers_LastChanged]  DEFAULT (getdate()),
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

You may have to post a full code from retrieve from db, modification and transfer to db. It is possibility a datetime value is lost since the default .NET DateTime value is 0001/01/01 which is violated the SqlServer datetime minimum value. — Zay Lau
– Zay Lau, Commented Sep 12, 2016 at 2:26
Literally, to isolate the issue I just retrieved the data and immediately tried to submit it. No changes were made between. I used debug to view the Customer object. Here are the values that I found: DateCreated: 11/1/1996, RenewalDate: 12/7/2015, VoidedDate: NULL, ExpirationDate: 12/7/2016 and LastChanged: 5/14/2016. — Ebassador
– Ebassador, Commented Sep 12, 2016 at 23:09

Zay Lau · Accepted Answer · 2016-09-13 01:37:22Z

0

Please be noted that if you are having VoidedDate = NULL (Nothing) in the debugger, that means 0001-01-01 in .NET.

There is no null for DateTime object in .NET

If you would like to debug, try select data into a datatable and then update with the exact same datatable.

answered Sep 13, 2016 at 1:37

Zay Lau

1,8641 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ebassador Over a year ago

I find that hard to believe. The table is defined to allow for NULL values and this code has been working for many months. Most of the accounts have a null value in the VoidedDate field. Why is this just now an issue?

Ebassador Over a year ago

FinDB is a DataContext used by Linq to SQL. In the IDE, the VoidedDate property shows Nullable = True.

Zay Lau Over a year ago

Any system/version upgrade? Any modification on the custom object class? Does this happen to all records or just one?

Ebassador Over a year ago

MSFT VS Community 2013 Version 12.0.40629.00 Update 5; .NET Framework Version 4.6.01055. So far, this account is the only one that has raised this issue. It is possible that there are others but I still am not satisfied that I know what is causing it.

Ebassador Over a year ago

Project also references System.Data.Linq version 4.0.0.0.

|

Community · Accepted Answer · 2017-05-23 12:32:47Z

I found an astute observation in another StackOverflow thread.

SubmitChanges() applies every change since you last called SubmitChanges() or created the context. You can determine what all changes are involved by looking at db.GetChangeSet(). Any date/time column on any affected object could be the cause - for instance, if any datetime comes through with a default of DateTime.MinValue, it is out of range for SQL Server. – mellamokb Apr 17 '13 at 16:11

This is my problem!! I tried the submit before getting the new value for BQCust and still got the error. I have never used the GetChangeSet method before but I will now. Thanks for the help!

Collectives™ on Stack Overflow

SQLDateTime Overflow and All DateTime fields are Valid

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related