CODEWITHSUNDEEP
c#sql.netexcel
Jimbo James
Jimbo James

Reputation: 727

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 statemwnt.

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?

Upvotes: 1

Views: 2253

Answers (5)

Keith
Keith

Reputation: 21244

Use DateTime.FromOADate()

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

Upvotes: 0

Martin Liversage
Martin Liversage

Reputation: 106906

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

Upvotes: 0

billinkc
billinkc

Reputation: 61239

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

Upvotes: 0

phoog
phoog

Reputation: 43056

Use DateTime.FromOADate(double d):

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

Upvotes: 0

Marc B
Marc B

Reputation: 360762

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.

Upvotes: 2

Related Questions