Date time error in c# while inserting data base?

Question

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)

Jenith Thakkar · 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

Karan Thakkar · Answer

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?

Karan Thakkar · Answer

its not store porcedure or sql query I am retrving data from excel and inserting into data base using sql bulkcopy sir.

Jignesh Kumar · Answer

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))

Abhishek Sur · Answer

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 ?