SQL Server

Epoch Differences Between VBA and SQL Server

Do you have unexplained dates of December 30, 1899 in your SQL Server database? VBA's epoch (i.e., zero date) could be the culprit.

Mike Wolfe

Dec 16, 2022 • 4 min read

That's not a typo.

While there are epic differences between VBA and SQL Server, we will be talking about epochs in this article.

What is an Epoch?

In programming, an epoch is the zero date.

Almost all programming languages treat dates as numbers. This simplifies date math. Finding the difference between two dates becomes nothing more than a subtraction operation. Of course, the numbers by themselves lack context. I know that date 44,911 is one week after date 44,904 because the difference between the two numbers is 7.

What I really want to know, though, is what those numbers translate to on the Gregorian calendar. And to do that, we need to decide what Gregorian calendar date the number zero should correspond with.

This so-called "zero date" is known as the epoch.

What is VBA's Epoch?

Learning the epoch of most programming languages is usually trivial. You just convert the number zero to a date type. In VBA, we use the CDate() function:

Okay, maybe it's not that trivial. I forgot that VBA assumes–for default formatting purposes–that any date variable where the date portion is set to the epoch (i.e., 0) is actually a time-only value. We have to force VBA to show us the date portion in this situation. We'll do that with the Format() function:

Aha! The VBA epoch is December 30, 1899.

What is SQL Server's Epoch?

I don't know, probably the same as VBA's, right? Let's check it out:

SELECT FORMAT(CAST(0 AS datetime), 'MMMM d, yyyy')

Well, this is awkward.

The SQL Server epoch is January 1, 1900.

VBA and SQL Server Have Different Epochs. No Big Deal, Right?!?!

The SQL Server epoch is January 1, 1900.

The VBA epoch is December 30, 1899.

So, if you set a SQL Server datetime field to '3:00 PM' it will store it as 0.625 (15/24).

Similarly, if you set a VBA Date variable to #3:00 PM# it will store it as 0.625 (15/24).

Note that times are stored as fractions of a day in both VBA and SQL Server.

Thus, if you convert a VBA Date variable with a value of 0.625 to a string, it will convert it as the system's default short time format, 3:00:00 PM. If you make that part of a dynamic SQL Server string, SQL Server will store it internally as 0.625 (1/1/1900 3:00 PM).

However, if you format the VBA Date variable and force it into UTC format, it will convert to 1899-12-30 15:00:00. If you then assign that value to SQL Server as part of a dynamic SQL Server string, then SQL Server will store it not as 0.625 but as -1.375. Since the date value is no longer zero, Access and VBA will not treat this as a time-only value when it is returned from SQL Server:

Moral of the Story

Don't generate dynamic SQL statements that convert time-only VBA Date values into strings with date formatting for use with SQL Server datetime fields.

There's a fair amount of information packed into that last sentence, so let me break down the required conditions for this to be a problem:

You are generating SQL on the fly at runtime
You are storing time-only values
The time-only values are stored in VBA Date variables
You are converting those values into strings
These string literals are being used in your dynamic SQL
The string literals are explicitly formatted to include the date (in addition to the time)
The target database is SQL Server
The target field type is a datetime

That may seem like a lot of conditions to go wrong all at the same time for this to be a problem in the real world. But let me assure you that I...uh...know a guy that this happened to recently. Real swell guy, but despite claiming to know better, he still sometimes writes code that's a bit too clever. But that's a story for another day...