Re: COleDateTime base date 30 Dec 1899. Why??
On Wed, 21 May 2008 20:57:55 +0200, Colin Peters <cpeters@coldmail.com>
wrote:
Hello all,
Not really an MFC question as such but I wondered if anybody knew.
Basically, I was working on a (MFC)project where an ADO recordset is
populated from an SQLServer table with a large range of date records.
The idea was to get more than I need then use the Filter property to
show just a limited set. The reason for this was to only have one SQL
Server round trip to fetch all the records for likely timespans.
Anyway I ran into trouble because the ADO Filter expects dates in local
format. To cut a long story short I outputted the SQLServer date as a
floating point number then convert the COleDateTime's also to float and
filter based upon the floats. This works fine, although you have to take
into account the different base date for the two data structures.
SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable
to me. COleDateTime has a base date of 30th Dec 1899. OK the difference
is 2 so it doesn't really matter, but I'm struggling to think of a
reason why that particular date was chosen.
Anyone know why?
I think it ultimately has to do with Lotus 123, whose date format Excel
copied, including the bug which considered 1900 to be a leap year. Consider
this little program:
#include <afx.h>
#include <ATLComTime.h>
#include <stdio.h>
void print(double serial)
{
COleDateTime d;
d.m_status = COleDateTime::valid;
d.m_dt = serial;
printf("%f = %s\n", d.m_dt, (LPCSTR) d.Format());
}
int main()
{
print(59);
print(60);
print(61);
print(62);
}
Its output is:
59.000000 = 2/27/1900
60.000000 = 2/28/1900
61.000000 = 3/1/1900
62.000000 = 3/2/1900
Looks fine. Now consider the same values in Excel:
59 2/28/1900 12:00:00 AM
60 2/29/1900 12:00:00 AM
61 3/1/1900 12:00:00 AM
62 3/2/1900 12:00:00 AM
Again, 1900 was not a leap year, so Excel 2007 is still copying the Lotus
bug and I suppose always will. Notice that the formatted COleDateTime
values are one day less than the Excel values for serial numbers < 61, and
that they agree for serial numbers >= 61; the COleDateTime does not
incorrectly consider 1900 a leap year. So based on this, I'd guess that the
designers of the OLE DATE type wanted to be compatible with Excel, but they
didn't want to copy the Lotus bug, and they were willing to accept being
incompatible with Excel for this small range of early dates. Given the
popularity of Lotus 123 back in the day, the Excel designers no doubt felt
they couldn't afford even this small degree of incompatibility.
--
Doug Harrison
Visual C++ MVP