What should be a default datetime value?

Question

I am inserting Excel Sheet records in my DataTable in c# and passing this DataTable to an SQL stored procedure. In my c# code I have put certain checks for empty data cells of Excel sheet to avoid Exceptions. But It seems like I am missing something while giving a default value for my SQL Date field.

string InvoiceDate = (row.Cells[3].Text == "&nbsp;") ? "0/00/0000 00:00:00 AM" : (row.Cells[3].Text);

And I get the following error:

String was not recognized as a valid DateTime.Couldn't store <0/00/0000 00:00:00 AM> in InvoiceDate Column. Expected type is DateTime.

Edited - Declaration of SQL field [InvoiceDate]

[InvoiceDate] [date] NOT NULL

Please don't suggest inserting null as I cannot Insert null for this column.

possible duplicate of [SQL - Insert NULL into DateTime](http://stackoverflow.com/questions/5362446/sql-insert-null-into-datetime) — Tanner, Jun 10 '15 at 10:58
Why are you passing string, if your database column is datetime? — Giorgi Nakeuri, Jun 10 '15 at 10:59
@GiorgiNakeuri When I `debug` my code with Excel sheet containing `5/31/2015` in Date column I get `5/31/2015 12:00:00 AM` in my `InvoiceDate` variable — Nuke, Jun 10 '15 at 11:28

Zohar Peled · Accepted Answer · 2019-03-27T14:10:35.900

4

First, There is no 00/00/0000 date, not in the real world and not in sql server.

Second, why do you even need a default values? just use null instead.

Third, use ISO 8601 format for specifying dates in strings (yyyy-mm-ddThh:mm:ss)

Forth, As Giorgi rightfully pointed out in his comment, why even use strings for a datetime value? use a variable of type DateTime in c#. note that it's min value is different then the sql server DateTime data type min value.

If your datetime column is not nullable, you can use 1753-01-01 (min value for datetime) or 9999-12-31 (max value for datetime)

One last thing, you might want to consider using either datetime2 or separate the date and time to different columns (data types date and time, of course). Why? read here.

edited Mar 27 '19 at 14:10

answered Jun 10 '15 at 11:03

Zohar Peled

79,642
10
69
121

According to the design of the table, I cant insert NULL values in my column. – Nuke Jun 10 '15 at 11:29
I've already included a part for such situation in my answer: **If your datetime column is not nullable, you can use 1753-01-01 (min value for datetime) or 9999-12-31 (max value for datetime)** – Zohar Peled Jun 10 '15 at 11:48

score 0 · Answer 2 · edited Jun 10 '15 at 12:12

0

Try to insert the current date instead:

string InvoiceDate = string.IsNullOrEmpty(row.Cells[3].Text) ? DateTime.Now.ToString() : (row.Cells[3].Text);

edited Jun 10 '15 at 12:12

thomasb

5,816
10
57
92

answered Jun 10 '15 at 11:03

Mukesh Kalgude

4,814
2
17
32

Please remove `? "0/00/0000 00:00:00 AM"` part from your code ;). – shA.t Jun 10 '15 at 11:31

What should be a default datetime value?

2 Answers2