Convert extracted date from excel to SQL date

I am extracting data from an Excel spreadsheet using interop in C# and I have a small problem that I cant think of an answer for.

When I extract the data for date cell using this code:

string _date = xlWorksheet.get_Range("B3", "B3").Value2.ToString().Trim();

I get a value of 40694 which wont go directly in to SQL using my insert stat开发者_开发技巧emwnt.

I have also tried:

DateTime _date = Convert.ToDateTime(xlWorksheet.get_Range("B3", "B3").Value2.ToString().Trim());

But that comes back with an error saying that it cant convert it.

Can anyone advise me on how to do it?

Excel's internal date values are "days since the epoch", which depends on if it's in PC or Mac mode (PC version uses 1/1/1900, Mac version uses 1/1/1904), and then there's an extra setting to be bug-compatible with Lotus 1-2-3 which has some leapyear issues. Converting this number realiably requires that you check if the spreadsheet is Windows- or Mac-based, and if the 1-2-3 compat flag is on.

You might be better of having Excel format the string into an unambiguous string (like 1-jan-1904) and then parse that back to a datetime value in SQL server, rather than trying to duplicate Excel's complicated date handling logic.

Use DateTime.FromOADate()

Using your example: DateTime _date = DateTime.FromOADate(Double.Parse(xlWorksheet.get_Range("B3", "B3").Value2))

Use DateTime.FromOADate(double d):

DateTime.FromOADate((double)(xlWorksheet.get_Range("B3", "B3").Value2))

Ran into the same thing, here's the conversion

    /// <summary>
    /// Seriously?  For the loss
    /// <see cref="http://www.debugging.com/bug/19252"></see>
    /// </summary>
    /// <param name="excelDate">Number of days since 1900-01-01</param>
    /// <returns>The converted days to date</returns>
    public static DateTime ConvertXlsdtToDateTime(int excelDate)
    {
        DateTime dt = new DateTime(1899, 12, 31);

        // adjust for 29 Feb 1900 which Excel considers a valid date
        if (excelDate >= 60)
        {
            excelDate--;
        }

        return dt.AddDays(excelDate);
    }

Excel stores dates as a floating point number counting the number of days since the day before 1900-01-01 (or 1904-01-01 for Mac). There is also a leap-year issue you have to take into account if the date is before 1900-03-01.

The following code will do the conversion:

DateTime ConvertToDateTime(Double date) {
  if (date < 1)
    throw new ArgumentException("Excel dates cannot be smaller than 1.");
  var epoch = new DateTime(1900, 1, 1);
  if (date > 60D)
    date -= 2;
  else
    date -= 1;
  return epoch.AddDays(date);
}