SQLDateTime OverFlow with Correct DateTime Format

Question

below is my code snippet where I insert a DateTime value to SQL database and encountered the following error:

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

deliveryCart.DeliveryDate = (DateTime)row.DeliveryDate;

Log.SaveLog(ConfigurationManager.AppSettings["LogFilePath"] + @"\Log.txt", "deliveryCart.DeliveryDate: " + deliveryCart.DeliveryDate.ToString());
//*value retrieved is 12/31/2019 12:00:00 AM

dataContext.Q_TBL_SUBMITTED_DELIVERY_CARTs.InsertOnSubmit(deliveryCart);

May I know if there is a format to be used? when converting the value of row.DeliveryDate?

Does this answer your question? Error - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM — SᴇM
– SᴇM, Commented Dec 20, 2019 at 7:08
Does that deliveryCart have any other dates, that may be at DateTime.MinValue? What are the properties of deliveryCart and what are the columns (and their types) of the table? — Hans Keﬆing
– Hans Keﬆing, Commented Dec 20, 2019 at 7:24

Never Die · Accepted Answer · 2019-12-20 07:17:48Z

1

actually the problem is SQL DateTime =/= C# Datetime

you need to change 2 things

Database change the field type from DateTime to DateTime2
Query you need to be explicit

you can find futher informations here,here and here

answered Dec 20, 2019 at 7:17

Never Die

3511 silver badge5 bronze badges

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

Comments

Gauravsa · Accepted Answer · 2019-12-20 07:13:25Z

0

If you are using dateTime.toString(), then need to ensure that the string is formatted per:

deliveryCart.DeliveryDate.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");

Also, DateTime is a value type and not a reference type. So, it cant be null. Value types if not initialised have a value of 0. For Datetime, it can be DateTime.MinValue which is outside Sql Server's value.

answered Dec 20, 2019 at 7:13

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

1 Comment

Hans Keﬆing Over a year ago

You shouldn't pass that date value as string to SQL, but as (the DateTime value of) a parameter of some date type. So no formatting needed.

Collectives™ on Stack Overflow

SQLDateTime OverFlow with Correct DateTime Format

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related