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 == " ") ? "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.

Answer 1

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.

Answer 2

Try to insert the current date instead:

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