DateDiff and regional settings

Question

I've got a VBscript that runs on devices placed all over the world and therefore uses various regional settings.

A part of my script is calculating the difference between 2 dates, but I can't get that working with a consistent result.

An example:

'Get system date in EU format    
dEUDate = Right("0" & DatePart("d",Date), 2) & "-" & Right("0" & DatePart("m",Date), 2) & "-" & DatePart("yyyy",Date)

'Read date/time value from an application in registry - will return e.g. 05-01-2013 07-19-00
dateKLAVDef = UCase(objShell.RegRead(strKLAVStateKey & "\Protection_BasesDate"))

'Find date difference
iAVDefAge = DateDiff("d", Left(dateKLAVDef, 10), dEUDate)

WScript.Echo "AV def.: " & dateKLAVDef & vbTab & "Current date: " & dEUDate & vbTab & "Diff: " & iAVDefAge

This is the result when the device is configured with EU regional settings which gives the correct result:

AV def.: 05-01-2013 07-19-00 Current date: 07-01-2013 Diff: 2

This is the result when the device is configured with non-EU regional settings (e.g. English (United States)) which (from the scripts point of view) is incorrect:

AV def.: 05-01-2013 07-19-00 Current date: 07-01-2013 Diff: 61

How can I get this working with non-EU regional settings?

Thanks in advance.

Separate the actual date from the presentation. Use `Now()` or `Date()` to create `dEUDate` and use this for calculations and when you want to create your own date, use `DateSerial()`. Finally when you want to display your date, use `FormatDateTime`, a dotnet stringbuilder or your own method to present the date, but do not reuse this representation string in your calculations. — AutomatedChaos, Jan 07 '13 at 09:53
Hi @AutomatedChaos that looks like a pretty good answer to me. — Fionnuala, Jan 07 '13 at 11:26
To add a little more explanation: what happens is that one case (EU) the date strings are interpreted as DD-MM-YYYY, whereas in the other case (US) the date strings are interpreted as MM-DD-YYYY. That's why in one location the difference is 2 days, while it's 2 months in the other. — Ansgar Wiechers, Jan 07 '13 at 11:54
I originally used Now() instead of dEUDate, but that doesn't solve the issue as the result will then be like below: AV def.: 05-01-2013 11-41-00 Current date: 1/7/2013 Diff: -114 Unless I totally have misunderstood your point!? ;) — Michael G, Jan 07 '13 at 14:34
`DateDiff()` interprets the strings "05-01-2013" and "07-01-2013" as May 1st and July 1st respectively, thus leading to the difference of 61 days. If you change the latter value to an actual date (1/7/2013, i.e. January 7th 2013) you get a difference of -114 days instead. Do **not** rely on formatted strings when dealing with dates, unless you parse these strings into actual date values yourself. — Ansgar Wiechers, Jan 07 '13 at 19:09

score 1 · Accepted Answer · edited Jan 07 '13 at 19:11

It seems dateKLAVDef is always in dd-MM-yyyy hh-mm-ss. Is that correct? Then you have to retrieve the day, month and year from this string and make it a Date with DateSerial(y, m, d). It will automatically transform to the correct locale formatting. Do a DateDiff with the current date et voila, you are ready:

' Read date/time value from an application in registry - will 
' return e.g. 05-01-2013 07-19-00
dateKLAVDef = "05-01-2013 07-19-00"

' Define a pattern for the given string
dateKLAVDefPattern = "^(\d+)-(\d+)-(\d+) .*"

' Make a regular expression
Set re = new RegExp
re.Pattern = dateKLAVDefPattern

' Execute the regex
Set matches = re.Execute(dateKLAVDef)

' Retrieve the submatches from the match
dateKLAVDefDay = matches(0).submatches(0)
dateKLAVDefMonth = matches(0).submatches(1)
dateKLAVDefYear = matches(0).submatches(2)

' recreate the dateKLAVDef date as a real date object
dateKLAVDefAsDate = DateSerial(dateKLAVDefYear, dateKLAVDefMonth, dateKLAVDefDay)

' Do a datediff on the created date and the current date
iAVDefAge = DateDiff("d", dateKLAVDefAsDate, Date())
MsgBox iAVDefAge

Thank you very much - that's working :) It seems like a lot of work for a relatively simple thing, though, but the important thing is that it solves my problem. — Michael G, Jan 07 '13 at 21:03
Well, you could do the same in this oneliner: `a=split(split(dateKLAVDef," ")(0), "-") : msgbox DateDiff("d", DateSerial(a(2), a(1), a(0)), Date)`, but it is cleaner to well document your code and perform each action in a separate statement. The separation of pattern and execution (like we did in the regex) keeps your code configurable. You'll never know when you see your code again and if you still can understand it at that time. — AutomatedChaos, Jan 08 '13 at 12:55

DateDiff and regional settings

1 Answers1