> But that does not seems to be the whole story, since if I do:
>
> SELECT datetime(42713.19166666667+julianday('1900-01-01'));
>
> I get 2016-12-11 04:36:00 instead of what you were expecting
> 2016-12-09 05:15. Something is a little off. I suspect that there
I think he is expecting 2016-12-09 04:36 (column "Excel values" looks
somehow reversed), so the time part is working.
> You are apparently wanting to use the Excel date format, which appears
> to be unique to Excel and used nowhere else. According to the
> Microsoft documentation
> (https://support.microsoft.com/en-us/kb/214094) Excel for Windows uses
> 1900-01-01 as its epoch and advances the value by 1.0 per day. Excel
> for Mac reportedly uses 1904-01-01 as the epoch. So straight away we
> see that you are going to need to modify your code depending on
> whether you are running on Windows or Mac.
Apparently one needs to add 2 to that value, since:
- 1900-01-01 in Excel (on Windows) gets value 1, not 0
- Excel notoriously and incorrectly considers 1900 a leap year [1] and
acknowledges it, but "won't change" [2]
So you should probably try
SELECT datetime(2 + 42713.19166666667+julianday('1900-01-01'));
Regards,
Daniel
[1] https://en.wikipedia.org/wiki/Leap_year_bug
[2] https://support.microsoft.com/en-us/kb/214326
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
