0

I am importing an Excel file into SQL server. My date column is not exactly imported as it is in Excel. The time is changed. For example, '2015-08-11 6:44:31 PM' from Excel '11-Aug-2015 06:43:12 PM' to SQL (please ignore format, I changed it)

Why the time is changed?

I'm not getting desired results where I compare datetime with DB to avoid duplicates while import. Here is the that part of code:

if (cell != null)
{

    if (cell.ColumnIndex == 0)
    {

        double d = double.Parse(cell.Text);
         conv = DateTime.FromOADate(d);

         DataTable dtx = DBConn.getDataTable("Select * from MyTable where DDate = '" + conv + "'");

         if (dtx.Rows.Count > 0)
         {
             ForceContinue = true;
             break;
         }
         else
         {
             newRecordCount++;            //new record count ^                            
         }

      Temp1 = Temp1.Replace("<DDate>", conv.ToString("yyyy-MM-dd hh:mm:ss tt"));
    }
}
pnuts
  • 58,317
  • 11
  • 87
  • 139
Vik
  • 89
  • 2
  • 13
  • if the date in excel is `11-Aug-2015 06:43:12 PM` then why not do this instead and covert the data into a true datetime `var someDateTime = Convert.ToDateTime(cell.Text);` for example run this code and you will see what I mean `var someDateTo = Convert.ToDateTime("11-Aug-2015 06:43:12 PM");` – MethodMan Sep 14 '15 at 22:07
  • @MethodMan - actually, this is not the BKM. The OP is using `DateTime.FromOADate` [which is a better way to go](http://stackoverflow.com/questions/18358275/convert-time-formatted-column-in-excel-to-c-sharp-datetime) – Yosi Dahari Sep 14 '15 at 22:09
  • then I would think that if `DataTime.FromOADate` would return the correct format but if not then perhaps converting the string from that cell to a datetime would provide correct results without any difference in time.. but it's hard to tell if the OP did not use the debugger to provide more accurate information.. – MethodMan Sep 14 '15 at 22:12
  • Can you try the `FromOADatePrecise` static method on this thread? http://stackoverflow.com/a/13922172/904156 – Loathing Sep 15 '15 at 00:10
  • @Loathing thanks for the link. I tried to do that way but not quite getting how to use it in my code. – Vik Sep 15 '15 at 00:39
  • If you replace `DateTime.FromOADate(...);` with `FromOADatePrecise(...);` do you get the correct date? – Loathing Sep 15 '15 at 02:26

0 Answers0