Region Independent Date/Time Formatting in VBScript

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 settled on using would work irrespective of a machine’s regional settings. This lead 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 which is 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:

21st December 2019 21:27:04
Regional Format dtsNow dtsIso8601
English (United Kingdom) 21/12/2019 21:27:04 2019-12-21 21:27:04
English (United States) 12/21/2019 9:27:04 PM 2019-21-12 9:27:04

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):

21st December 2019 21:27:04
FormatDateTime(Date[, NamedFormat])

If the NamedFormat argument is omitted, vbGeneralDate is used.
Regional Format: English (United Kingdom) Regional Format: English (United States)
FormatDateTime(Now) 21/12/2019 21:27:04 12/21/2019 9:27:04 PM
FormatDateTime(Now, vbGeneralDate) 21/12/2019 21:27:04 12/21/2019 9:27:04 PM
FormatDateTime(Now, vbLongDate) 21 December 2019 Saturday, December 21, 2019
FormatDateTime(Now, vbShortDate) 21/12/2019 12/21/2019
FormatDateTime(Now, vbLongTime) 21:27:04 9:30:27 PM
FormatDateTime(Now, vbShortTime) 21:27 21:27

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:

21st December 2019 21:27:04
Regional Format dtsNow dtsIso8601
English (United Kingdom) 21/12/2019 21:27:04 2019-12-21 21:27:04
English (United States) 12/21/2019 9:27:04 PM 2019-12-21 21:27:04

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:

21st December 2019 21:27:04
Regional Format dtsNow dtsIso8601
English (United Kingdom) 21/12/2019 21:27:04 2019-12-21 21:27:04
English (United States) 12/21/2019 9:27:04 PM 2019-12-21 21:27:04

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:

Statement Result
CDate(-1) 29/12/1899
CDate(0.5) 12:00:00
CDate(1) 31/12/1899
CDate(1.5) 31/12/1899 12:00:00

0 Comments

Post a comment

All comments are held for moderation.
Your email address will not be published. Required fields are marked *

Identification
Email
Name *
Website

Copyright © 2018 - 2020 thecliguy.co.uk
For details, see Licences and Copyright