Well, no. Microsoft intended to store the number of days since 1900/01/01 with 1900/01/01 being day 1. Unfortunately, Microsoft being Microsoft does not know how to calculate leap years properly -- I think they still have problems with the divisible by 100 and 400 bits, and will probably have trouble with the divisible by 4000 bits if they are still around then.
Since the base date contains a leap year that does not exist, the epoch base is 1899/12/30. Also note that OLE Time is the only one where the epoch date is in localtime, not in UTC/Zulu, which makes getting the "correct" result somewhat problematic, if you care about timezones. (ie, OLE stamping is a wallclock stamp, not a proper timestamp) > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Paul Sanderson > Sent: Tuesday, 13 December, 2016 08:37 > To: SQLite mailing list > Subject: Re: [sqlite] Date (from REAL) with Negative Year > > rather unintuitively excel uses the OLE automation timestamp that > records the number of days since 1899/12/30 > > http://sandersonforensics.com/forum/content.php?131-A-brief-history-of- > time-stamps > > https://msdn.microsoft.com/en- > us/library/system.datetime.tooadate(v=vs.110).aspx > > now no need for the ugly +2 > > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic- > Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 13 December 2016 at 13:19, Daniel Dumitriu <daniel.dumit...@gmail.com> > wrote: > >> 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
