0

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?

gymcode
  • 4,431
  • 15
  • 72
  • 128
  • 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](https://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000-am-and-12-31-999) – SᴇM Dec 20 '19 at 07: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 Kesting Dec 20 '19 at 07:24

2 Answers2

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

Never Die
  • 331
  • 1
  • 5
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.

Gauravsa
  • 6,330
  • 2
  • 21
  • 30
  • 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. – Hans Kesting Dec 20 '19 at 07:22