VARIANT DATE to datetime string

A long, long time ago, in software development...
There was a VARIANT type, which could hold either of many other datatype values (vartypes), see wikipedia. And one of the vartypes was VT_DATE, aka Variant date, aka DATE (double), aka OLE variant date. It was a DateTime value represented as a double - 8-byte floating-point number. BTW, everything about variant data type is still relevant today, except that hardcore Windows or COM/ActiveX/OLE programming does not look mainstream nowadays.

Sometimes, when you see something like 42842.370277778, the chance is that it is a variant date. And here is a simple calculator which takes a double value, interprets it as the variant date, and outputs date and time encoded in this double value. Those who are curious about the format of variant dates and the history behind them can continue to read after the calculator.
.

VARIANT DATE to datetime string

Variant date

Calculate

Datetime

 

 Link  Save  Widget

About variant date format.

According to documentation, days are represented by whole number increments starting with 30 December 1899, midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number.

Yet, there is a catch. Although day values become negative before midnight on December 30, 1899, time-of-day values do not. For example, 6:00 AM is always represented by a fractional value of 0.25 regardless of whether the integer representing the day is positive (after December 30, 1899) or negative (before December 30, 1899).

Due to this, the value of -1.25 represents 12/29/1899 06:00 AM, and the value of -1,30 represents 12/29/1899 07:12 AM. That is, while the second double value is less than the first double value, as the date, it is greater than the first date.

Also, date values between -1.0 and 0.0 represent the same logical dates as their positive counterparts. That is, -0.5 and 0.5 are the same 12/30/1899 12:00.

You may also wonder why the variant date has such a strange zero point - 30 December 1899, as opposed to 1 January 1900 (SQL Server zero point) or 1 January 1970 (Unix/Javascript zero point). As written by Eric Lippert, "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, but apparently, Lotus 1-2-3 used this date format, but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day." This and the other VT_DATE oddities are explained in his article, and it is fascinating reading for an old developer like me. Enjoy.