Excel/SQLite date serial numbers

Question

How can I get SQLite to convert excel-type serial numbers to dates, e.g. I want the integer 40074 in a table to somehow get the date 18-Sept-2009?

The dates are already in SQLite.

Answer 1

Excel stores dates as the number of days since 30-Dec-1899 so to convert a number to/from a date, you simply have to calculate the number of days between 30-Dec-1899 and the date you're after. How you do that depends on exactly which language you're working in, but for C# for example, the following would work:

var epoch = new DateTime(1899, 12, 30);

int number = 40074;
var date = epoch.AddDays(number);
Console.WriteLine(date);

var date = new DateTime(1987, 5, 3);
int number = (int) date.Subtract(epoch).TotalDays;
Console.WriteLine(number);

(If you're wondering why the epoch is 30/12/1899, it's because the actual epoch is supposed to be 1/1/1900 but Excel erroneously assumes that 1900 was a leap year)

Answer 2

This seems to work:

sqlite> SELECT DATETIME((49400 * 3600 * 24) - 3014928000, 'unixepoch');
2009-09-18 00:00:00

Honestly, I just guess and checked on the constant there, but I'm sure there's simple math to back it up.

It looks like it works for dates earlier than the epoch as well, but I haven't tested it thoroughly.

Answer 3

sqlite> SELECT DATE('1899-12-30', '+40074 days');
2009-09-18