The dates in Excel, VBA and SQL Server

There is a lot over the Internet, written for the dates in Excel, VBA and SQL Server, but still, there is quite much to be written.

In general, it all has started, when the developers of Lotus 1-2-3 have decided that a leap year is every fourth one. Unfortunately this is not the case, as the leap year rule is:

There is a leap year every year whose number is perfectly divisible by four – except for years which are both divisible by 100 and not divisible by 400. 

Thus, the Lotus 1-2-3 developers have decided that 29.February 1900 exists and they have implemented it in their spreadsheet solution. Then the guys from Microsoft, building Excel have decided to make their solution compatible with the one from Lotus 1-2-3 and they have built up the same error.

Later, in the introduction of VBA, the error was not duplicated, thus VBA and Excel have different date system from 01.January 1900 to 28.February 1900. SQL Server also has its own understanding for dates. To make it more complicated (or to ease the pain), Microsoft has introduced 1904 date system in Excel, thus introducing a fourth date system to the party. To summarize, this is what we have:

• Excel date
• Excel date with 1904 property
• VBA date
• SQL Server date

In general, the dates are converted to numbers. E.g., every date is converted to a number, but the starting number can be a bit different.

• In Excel, the 1 is converted to 01.January.1900;
• In VBA, the 1 is converted to 31.December.1899;
• In MSSQL Server, the 1 is converted to 02.January.1900 (SELECT CONVERT(DATETIME,1));
• In Excel, if you activate the ActiveWorkbook.Date1904=True property, the 1 is converted to 02.January.1904;