What would cause a SqlDateTime overflow when using LINQ to SQL SubmitChanges()?

Question

In my code I have multiple objects being added to the repository, I have tried running the repository Save() function one time at the end of all the loops, and also calling it after every object that is added. But either way I still get a SqlDateTime overflow when the db.SubmitChanges() in the repository .Save()... any idea?

 SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
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.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Source Error:

Line 155:        public void Save()
Line 156:        {
Line 157:            db.SubmitChanges();
Line 158:        }
Line 159:


Source File: C:\inetpub\wwwroot\Models\OrderRepository.cs    Line: 157


        foreach (string vol in volumes)
        {
            if (vol != "-1" && vol != "")
            {
                Product prod = orderRepository.getProductByVolumeID(System.Convert.ToInt32(vol));
                ProductVolume pvol = orderRepository.getProductVolumeByID(System.Convert.ToInt32(vol));

                OrderProduct oProd = new OrderProduct();
                oProd.OrderID = getOrder.OrderID;
                oProd.VolumeID = pvol.VolumeID;
                orderRepository.AddOrderProduct(oProd);


            }
        }

        foreach (string feat in features)
        {
            if (feat != "")
            {
                Product prod = orderRepository.getProductByID(System.Convert.ToInt32(feat));

                OrderFeature oFeat = new OrderFeature();
                oFeat.OrderID = getOrder.OrderID;
                oFeat.ProductID = prod.ProductID;
                orderRepository.AddOrderFeature(oFeat);


                featuresTotal += prod.UserIntervalPrice;
            }

        }

        totalTotal = volumeTotal + featuresTotal;
        orderRepository.Save();

Wyatt Barnett · Accepted Answer · 2009-09-29 18:48:18Z

5

Basically, the issue is that the Sql DATETIME datatype starts at 1/1/1753, wheras DateTime.MinValue is 1/1/0000 (or something like that). So, if you don't initialize the date properties, you get Sql overflows.

Options to fix:

a) Initialize the DateTime somewheres.

b) Switch sql to use the DATETIME2 datatype which does cover the whole range of .NET DateTime values. [SQL 2008 only]

edited Sep 29, 2009 at 18:48

answered Sep 29, 2009 at 18:02

Wyatt Barnett

15.7k3 gold badges37 silver badges54 bronze badges

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

4 Comments

MetaGuru Over a year ago

ah yes, I had totally forgot that I even had a DateTime field in my table, this answer refreshed my memory and explained why, thanks

Wyatt Barnett Over a year ago

fixed answer to account for RichardOD's comment

DazManCat Over a year ago

Could you elaborate on the initialise the date properties bit?

Wyatt Barnett Over a year ago

MyObject.MyDateTime = DateTime.Now. You know, you just set the variable to be something sql won't barf on.

jrummell · Accepted Answer · 2009-09-29 18:27:48Z

1

You can validate your dates in the OnValidate() partial method using SqlDateTime.MinValue and SqlDateTime.MaxValue.

answered Sep 29, 2009 at 18:27

jrummell

43.2k18 gold badges116 silver badges179 bronze badges

Collectives™ on Stack Overflow

What would cause a SqlDateTime overflow when using LINQ to SQL SubmitChanges()?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related