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

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 2

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