Eric’s Complete Guide to VT_DATE

I find software horology fascinating.

The other day, Raymond said “The OLE automation date format is a floating point value, counting days since midnight 30 December 1899. Hours and minutes are represented as fractional days.”

That’s correct, but actually it is a little bit weirder than that. I suspect that I may be the world’s leading authority on bugs having to do with the OLEAUT date format: a dubious distinction at best. I call it the VT_DATE format because it is the data stored in an OLEAUT variant of type VT_DATE.

Here are some interesting (well, interesting to me) facts about OLEAUT dates.

First of all, let’s start with the obvious problem — Midnight 30 December 1899 in what time zone? We never say. OLEAUT dates are always “local” which makes it very difficult to write code that uses OLEAUT dates — any VB or VBScript program, for example — which must deal with two things happening at the same time in different time zones.

Next, what about daylight savings time? How does one represent those days which due to springing forward or falling back have 23 hours or 25 hours? Again, those who use the OLEAUT date format need to pretend that these days do not exist.

Now let’s get really weird. An OLEAUT date is, as Raymond noted, a double where the signed integer part is the number of days since 30 December 1899 and the fraction part is the amount of that day gone by. So what is 1.75? That’s 6:00 PM, 31 Dec 1899. What about -1.75? That’s 6:00 PM, 29 Dec 1899. Notice how the 0.75 part means 6:00 PM in both cases; three-quarters of the way through the day.

What about 0.75 and -0.75? Uh, those are zero and “minus zero” days from 30 December 1899, again at 6:00 PM . Those two numbers are the same time. This means that any program which must calculate the difference between two OLEAUT dates must say that (-0.75) – (0.75) = 0 difference in time.

The reason I know all this is because my first ever checkin as a full timer was a rewrite of the VBScript implementations of DateAdd and DateDiff, both of which used to be a mass of spaghetti code to handle all the special cases entailed by the discontinuities between -1.0 and 1.0. Incidentally, I now ask interview candidates to write me those algorithms during interviews! (Attention potential interview candidates reading this: writing a mass of spaghetti code on my whiteboard is a bad idea. Solving this problem by cases is a bad idea. There are better ways to solve this problem!)

Here’s another bogus one: how about -1.99999999 and -2.0? Those are 0.00000001 apart in numbers but almost 48 hours apart in time! But OLEAUT dates are rounded to the nearest half second by the operating system, and you guessed it: any dates less than a quarter second before midnight before 30 Dec 1899 are sometimes “rounded” two days wrong. I wrote the code that converts OLEAUT dates to JScript dates, and it at least does correctly handle this case, though I had to jump through some hoops to do it.

(UPDATE: I have no idea if in the 16 years since I first wrote this article that bug in Windows was ever fixed. Anyone want to try it out and see?)

Some more oddities: The range is enormous, and the precision varies greatly over the range! You can represent dates long before the creation of the Universe, though you lose precision as you go. However, the only valid dates for the date format are between 100 AD and 10000 AD, and since we have half-second granularity, we are wasting a whole lot of bits here.

And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time; what I can tell you is that Lotus 1-2-3 used this date format, and wished to have “day one” be 1 January 1900. However, they forgot that 1900 was not a leap year, and therefore their “day count” was off by one for every day after 28 February 1900. Microsoft chose to use the Lotus date format for Excel, for compatibility, but “fixed” this bug by moving “day one” back one day. Therefore day zero is 30 December 1899.

Getting date code right is hard this is one of those areas where messy human requirements are hard to translate into crisp machine logic. There are huge localization problems for instance, in Japan it is legal to specify years as the fifth year of the reign of Emperor Hirohito . Thailand’s calendar doesn’t count from 1 AD. In Israel, the start and end days for daylight savings time are not standardized but rather are declared anew by the government after fierce debate every year.

These situations are fraught with peril for the unwary developer. I once drove our Microsoft support team in Israel to distraction by accidentally changing both Arabic and Hebrew locales to display dates right-to-left. Apparently in Arabic dates are customarily written right-to-left, but Israelis use left-to-right dates even when they are embedded in right-to-left Hebrew text.

You wouldn’t think that something as simple as asking “what day is it?” could lead to so many problems, but the world is seldom as cut-and-dried as we software developers would like.

For more history on this bizarre date format, see this article by Joel Spolsky.


1 thought on “Eric’s Complete Guide to VT_DATE

  1. Pingback: Porting old posts, part 1 | Fabulous adventures in coding

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s