Understanding Dates and Times in VBScript

The idea of this article was originally to document all of VBScript’s functions for handling dates and times. However, as I began writing I quickly realized that there is much confusion surrounding how dates and times are handled in scripting. I decided instead to write an entire series dedicated to the topic. In the process, I will hopefully help you learn VBScript’s date and time functions while giving you a better understanding of the underlying technology.

As you move in to this series, expect some confusion.  A lot of people believe that VBScript’s date and time functions are inherently buggy.  This is a huge misconception!  In fact, they were written to work around bugs in the underlying technology specifications.  You’ll see as we move along that you do not need to work around these bugs; the functions do it for you—if you use them as they were intended!

While reading these articles, you will undoubtedly come across some things you won’t understand.  Whether you’re a scripting beginner, or a seasoned programmer, it’s likely that you will see some information here with which you are unfamiliar.  I’ll make every attempt to simplify my explanations.  You may need to read something twice, or even move past it if you don’t understand it completely.  That’s okay.  I’ll make sure that the most important points are obvious.  As a scripter you really only need to know the hows and not the whys.  To do that, you will only require a basic understanding of a few key points.  I’ll make sure to list those at the end.

In scripting, you will use two very different date and time formats—with the possibility of a third.  We’re going to examine each of them one at a time and then look at how to use them together.

{mospagebreak title=VBScript’s VT_DATE Specification}

Microsoft Visual Basic Scripting Edition uses the VT_DATE specification for working with date and time values.  This is a Variant Date type that uses a 64-bit (8 bytes) signed floating point number to represent the number of days that have passed since midnight on a specific day in history, known as an epoch.  Hours and minutes are expressed as fractions of a day.

“A specific day in history?”

Yup.  Okay, that was a little vague.  The VT_Date specification expresses the number of days that have passed since 30 Dec 1899.

“Why that day?”

This is one of those things that is believed to be a bug, but really isn’t.  To explain it, you need to become familiar with the standard, so let’s look at some examples.

Let’s assume for once second that the epoch was midnight 31 Dec 2007 instead.  As I’m writing this, the current date is 1 Sep 2008.  That means that 244 days have elapsed since the epoch day.  Further, it’s 3:25 PM.  That’s 15 hours and 25 minutes, or 925 minutes that have elapsed since midnight.

We can express today’s date and time as a floating point number like VT_Date.  The integer portion of the number is the number of days that have elapsed since the epoch day.  There are 244 days that have elapsed.  That makes the integer portion equal to 244.

The time is expressed as a decimal fraction of a day.  We’ve already shown that 925 minutes have elapsed since midnight.  There are 1440 minutes in a day.  Simple division shows that 0.642361 days have passed since midnight.  Sure enough, at 3:25 PM that’s just about two-thirds of a day.

With all of this in mind, the current date and time would look something like this: 244.642361 if we were using midnight on 30 Dec 2007 as our zero point.  This is exactly how the VT_Date specification works—only it works with a zero point much further back in time.

{mospagebreak title=The Significance of 30 Dec 1899}

Okay, so back to the question at hand.  Why 30 Dec 1899.  Why not 1 Jan, 1900 to make calculations easier?  And what happened to 31 Dec 1899?  It makes no sense.  Or does it?

The idea was originally to simplify calculations by using the beginning of the century as a zero point on the time line.  This would have been midnight on 31 Dec 1899, thus the very first second that elapsed would have been on 1 Jan 1900, making it the very first second of the century, also making 1 Jan 1900 the first full day.  This seems like a very logical solution.  Unfortunately, human logic and computer logic aren’t always the same.

The date functions in VBScript (and originally Visual Basic) were largely taken from Microsoft Excel which already had a large set of built-in date functions.  Why reinvent the wheel?

There’s just one small problem.  If you examine the Excel functions that were used, you’ll run into a slight glitch.  January 1, 1900 is considered 1, the first full day.  The functions also show that February 28, 1900 is equal to 59.  And it’s true that 59 days had elapsed.  Great.  If you tried March 1, 1900 as a date it would return 61.  Uh-oh.

What happened to 60?  February 29, 1900?  There’s one minor problem—the year 1900 was not a leap year.  How could the Excel guys have missed that?  The truth is that they didn’t.  It was an imported bug.  Literally!

Excel was written with support for importing from Lotus 123, which displayed this bug.  In order to support imported dates, Excel needed to calculate them the same way.  The truth is that this was probably an intended bug in Lotus.  It had memory constraints, so it uses a simplified method of determining whether or not a particular year was a leap year.  The Lotus team probably wasn’t too worried about being wrong about the first two months of a year so long ago.

The VB team was; so rather than starting from January 1, 1900, they elected to make calculations from the previous year by moving the epoch back one day to December 31, 1899 instead.  Thus the starting point for time became midnight of December 30, 1899.

{mospagebreak title=Using the VT_Time Specification}

As I stated previously, the VT_Time Specification represents dates and times using a 64-bit (8 bytes) signed floating point number.  Because it is signed, the value may be positive or negative.  VBScript has two numeric data types that fit this specification: Date and Double.  Date was obviously created for this purpose.  VBScript handles values of type Date in a way that allows you to display dates very easily.  The numeric type Double can be used to display the actual 64-bit signed floating point number.

By default, Dates in VBScript will be displayed using the local short-date format.  This is based on the local system settings.

VBScript supports any date between 1 Jan 100 AD and December 31, 9999 AD.  In other words, it supports decimal values between -657434.000000 and 2958465.000000.  These constraints were used for two reasons.  First, this is pushing the limits available by the Double width, and second, because values less than 100 AD have a two digit year.

Attempting to use dates outside of the valid range will result in an error.  Unfortunately, this error varies depending upon how this occurred.  This makes debugging slightly difficult.  My best advice: any date read from the system should be valid, but you should validate any date entered by a user before attempting to assign it to a date variable or function.

In the twentieth century, the MM/DD/YY date format was acceptable.  Thus VBScript will accept two digits years, but it does assume that they occur between the years 1900 and 1999.  Since this can provide unpredictable results, it’s best to always use four digit years.

VBScript dates are also considered to be local time.  This means that they are calculated using the local Region and Time Settings.  In other words, there is no inherent support for different time zones.  Times are calculated based on the system’s current time zone.  From a programming standpoint, this can be troublesome.  Thus, any code that involves events occurring in separate time zones will need to have additional logic to handle the time zone changes.

For more detailed information about the shortcomings of VBScript’s dates and times, check out Eric Lippert’s blog on MSDN.

Now that you have an understanding of how dates and times are handled by VBScript, you’re ready to begin working with them in your code.  In my next article, I’ll show you the various date and time functions available in VBScript.  Until next time, keep coding!

[gp-comments width="770" linklove="off" ]