Region Independent Date/Time Formatting in VBScript

• Summary
• Problem
• What Not To Do
  • String Manipulation
  • FormatDateTime Function
• Solutions
  • Date/Time Component Functions
  • DatePart Function
• Further Reading
  • DisplayAndLog
  • Date Data Type

Summary

I recently had cause to work on some existing automation tasks written in VBScript. These scripts may well be replaced with PowerShell in the future but for now I just need to maintain the status quo.

I wanted to add functionality to write to a log file, prefixing each entry with an ISO 8601 formatted date/time. Furthermore, I wanted to ensure that the technique I adopted would work irrespective of a machine’s regional settings. This led me to reacquaint myself with the date formatting capabilities of VBScript, the details of which I’ll cover in this post.

Problem

Whilst VBScript boasts numerous functions for working with dates and/or times, it lacks a built-in function to output a date/time in a specific format which is not influenced by a machine’s regional settings.

The current date and time can be obtained with the Now function which returns the date and time as a data type of Date. The way in which the textual representation of a Date is displayed depends on a machine’s regional data formatting settings.

Read on for what not to do with the value returned by Now, followed by two possible solutions…

What Not To Do

String Manipulation

Something you should not attempt to do is manipulate the textual representation of the Date type that’s returned by Now, EG:

dtsNow = Now

dtsIso8601 = Left((split(dtsNow,"/"))(2),4) _
             & "-" & (split(dtsNow,"/"))(1) _
             & "-" & (split(dtsNow,"/"))(0) _
             & " " &  (split(dtsNow," "))(1)

wscript.echo dtsNow
wscript.echo dtsIso8601

This code is fragile because it relies on the textual representation adhering to the format dd/MM/yyyy hh:mm:ss. So while (for example) it works as desired on a computer that’s configured to use the default regional formatting settings for English (United Kingdom), it fails for English (United States). The table below demonstrates the impact a regional settings change has on the output of the two variables:

FormatDateTime Function

Another option to avoid is the FormatDateTime function which takes a date or time expression and formats it in accordance with the value supplied to its NamedFormat argument.

The reason for dismissing the use of FormatDateTime is because you can’t specify an exact date/time composition. It only supports a limited number of predefined formats all of which are influenced by a machine’s regional data formatting settings, so you can’t guarantee a consistent result from one machine to another. See examples below based on the defaults for English (United Kingdom) and English (United States):

Solutions

Date/Time Component Functions

VBScript possesses functions (Second, Minute, Hour, Day, Month and Year) for extracting specific date/time components from an expression representing a date and/or time.

These component functions can be used in conjunction to produce an ISO 8601 formatted date, EG:

dtsNow = Now

dtsDateIso8601 = Year(dtsNow) _
                 & "-" & Right("0" & Month(dtsNow), 2) _
                 & "-" & Right("0" & Day(dtsNow), 2) _
                 & " " & Right("0" & Hour(dtsNow), 2) _
                 & ":" & Right("0" & Minute(dtsNow), 2) _
                 & ":" & Right("0" & Second(dtsNow), 2)

wscript.echo dtsNow
wscript.echo dtsDateIso8601

The output produced by the dtsIso8601 variable is unaffected by regional settings:

DatePart Function

The DatePart function can extract an individual component (such as day or hour) from an expression representing a date and/or time, EG:

dtsNow = Now

dtsIso8601 = DatePart("yyyy",dtsNow) _
             & "-" & Right("0" & DatePart("m",dtsNow), 2) _
             & "-" & Right("0" & DatePart("d",dtsNow), 2) _
             & " " & Right("0" & DatePart("h",dtsNow), 2) _
             & ":" & Right("0" & DatePart("n",dtsNow), 2) _
             & ":" & Right("0" & DatePart("s",dtsNow), 2)

wscript.echo dtsNow
wscript.echo dtsIso8601

The output produced by the dtsIso8601 variable is unaffected by regional settings:

Further Reading

DisplayAndLog

As mentioned at the beginning of this post, the reason for researching the date/time formatting capabilities of VBScript was because I wanted to add logging functionality to an existing script, prefixing entries to the log with an ISO 8601 formatted date/time.

In case you’re interested, the function I wrote can be found here.

Date Data Type

This is a slight tangent from the subject of this post but it’s worthy of explanation.

In VBScript, a Date/Time is stored as a Date data type which is actually a Double (double-precision floating-point value) where the integer part denotes the number of whole days since 30th December 1899 and the decimal part denotes the fractional part of a day.

Therefore, the 31st December 1899 (the day after 30th December 1899) is represented as 1. This can be demonstrated using the CDate function, I’ve included a number of examples below: