Date time error in c# while inserting data base?

Karan Thakkar

7y868Learn iOS Programming

It insert wrong date when I am retreving date from excel?

The date format is:05-01-2019

It insert 2019-05-01 (database) incorrect

correct date is=2019-01-05

When date is greater than 12 it stores in correct format.

2019-12-25(database)correct

Excel(25-12-2019)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar7y ago
Accepted answer
Try to add culture info instead of to string
Like this

CultureInfo culture = new CultureInfo("en-US");
DateTime tempDate = Convert.ToDateTime("1/1/2010 12:10:15 PM", culture);

So your code will be like

if (DateTime.TryParse(Convert.ToString(dr[excelIndex]).Trim(), out temp))
{
value = Convert.ToDateTime(Convert.ToString(dr[excelIndex]).Trim(),culture);
}
else if (DateTime.TryParseExact(CommonUtils.EmptyIfNull(dr[excelIndex]), "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out temp))
{
value = Convert.ToDateTime(temp,culture);
}

Please try in this way and let me know if it helps for you
0
Karan Thakkar7y ago
This is my code.
if (Convert.ToString(dr[excelIndex]).Trim() != string.Empty)
{
DateTime temp; string formattedDate;
if (DateTime.TryParse(Convert.ToString(dr[excelIndex]).Trim(), out temp))
{
value = Convert.ToDateTime(Convert.ToString(dr[excelIndex]).Trim()).ToString("dd-MMM-yyyy");
}
else if (DateTime.TryParseExact(CommonUtils.EmptyIfNull(dr[excelIndex]), "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal, out temp))
{
formattedDate = temp.ToString("dd/MMM/yyyy");
value = Convert.ToDateTime(formattedDate);
}
what should i changed to solve the problem?
0
Karan Thakkar7y ago
its not store porcedure or sql query I am retrving data from excel and inserting into data base using sql bulkcopy sir.
0
Jignesh Kumar7y ago
Hi Karan,

While insert or while getting you need to change your logic for date field.
Please try belwo
Insert into TabaleName(datefieldNanem) values(CONVERT(datetime ,@YourParameterNameforDateField, 103))
0
Abhishek Sur7y ago
1. insert into table_name (date_field) values (TO_DATE('05-01-2019', 'dd-mm-yyyy'))
Why dont you try To_date function to specify the date format yourself like the example given ?
0