Clever Geek Handbook

What should be the default date and time value?

I am inserting Excel records into mine DataTable

in c#

and passing this DataTable to SQL stored procedure

. In my C # code, I put certain checks on the data cells of an empty

Excel sheet to avoid exceptions. But it looks like I am missing something by giving the value default

to my SQL Date

.

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

And I am getting the following error:

The string was not recognized as a valid DateTime. Couldn't store <0/00/0000 00:00:00 AM> in the InvoiceDate column. The expected DateTime type.

Edited - SQL Field Declaration [InvoiceDate]

[InvoiceDate] [date] NOT NULL

Please do not suggest to insert null

as I cannot insert null

for this column.

+3


source to share


2 answers


First , there is no date 00/00/0000

, neither in the real world nor in SQL Server.

Second , why do you need defaults? just use zero instead.

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

)



Fourth , as George correctly pointed out in his comment, why even use strings for a date and time value? use a variable of type in . note that this minimum value is different from the minimum value of the sql server datatype . DateTime

c#

DateTime

If your column is not null you can use (minimum for datetime) or (maximum for datetime) datetime

1753-01-01

9999-12-31

And lastly, you might consider using or splitting the date and time into different columns (data types and of course ). What for? read here. datetime2

date

time

+4


source


Try inserting the current date instead:



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


source






More articles:


All Articles