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, Sep 12 '16 at 02: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, Sep 12 '16 at 23:09

score 0 · Answer 1 · answered Sep 13 '16 at 01:37

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 '16 at 01:37

Zay Lau

1,856
1
10
17

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 Sep 13 '16 at 14:47
FinDB is a DataContext used by Linq to SQL. In the IDE, the VoidedDate property shows Nullable = True. – Ebassador Sep 13 '16 at 14:54
Any system/version upgrade? Any modification on the custom object class? Does this happen to all records or just one? – Zay Lau Sep 13 '16 at 14:54
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 Sep 13 '16 at 15:07
Project also references System.Data.Linq version 4.0.0.0. – Ebassador Sep 13 '16 at 15:09
What if checking all datetime value `if (VoidedDate == DateTime.MinValue) Insert(DBNull);` ? – Zay Lau Sep 13 '16 at 15:11
Just for jollies, I manuall set the VoidedDate value to a valid date and tried to submit it. Save results. – Ebassador Sep 13 '16 at 15:24

score 0 · Answer 2 · edited May 23 '17 at 12:32

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!

SQLDateTime Overflow and All DateTime fields are Valid

2 Answers2