I periodically run into an issue where the date [format] I'm using in my VBScripts (typically MM/DD/YYYY) don't behave as expected on various machines. Upon further inspection it has to do with the local regional settings on said machine.
What's the best method for ensuring that my dates are always interpreted/converted properly?
How can I make sure #11/2/2012# is treated as November 2, 2012 and not February 11, 2012?
Am I to create something from scratch that evaluates the regional settings on the machine then take action based on that?
For instance, our counterparts across the pond use the DD/MM/YYYY format while most in the US tend to use MM/DD/YYYY. A few people in various US offices not only use the same format as those across the pond, but they also use a different date separators: hyphen (-) or period (.) vs. slash (/). The combinations are endless!
I tried my hand at creating some functions but they don't always work, because someone is using some unexpected format. Can the community help me not only improve this code but also think through this problem situation?
Have I over complicated the situation?
HELP!!
Sincerely - Going bonkers!
What's the best method for ensuring that my dates are always interpreted/converted properly?
How can I make sure #11/2/2012# is treated as November 2, 2012 and not February 11, 2012?
Am I to create something from scratch that evaluates the regional settings on the machine then take action based on that?
For instance, our counterparts across the pond use the DD/MM/YYYY format while most in the US tend to use MM/DD/YYYY. A few people in various US offices not only use the same format as those across the pond, but they also use a different date separators: hyphen (-) or period (.) vs. slash (/). The combinations are endless!
I tried my hand at creating some functions but they don't always work, because someone is using some unexpected format. Can the community help me not only improve this code but also think through this problem situation?
Code:
Function DeployDate(targetDate1, targetTime1)
Dim targetDate, targetTime, SerializedDate, SerializedTime, SerializedDT
targetDate = targetDate1
targetTime = targetTime1
' Revisit: How to handle other separators?
' Is this even sound judgement/logic?
' Some people use periods (.) as separators when entering the date
If (IsInString(".",targetDate)) Then
targetDate = Split(targetDate,".")
End If
' Some people use slashes (/) as separators when entering the date
If (IsInString("/",targetDate)) Then
targetDate = Split(targetDate,"/")
End If
' Some people use hyphens (-) as separators when entering the date
If (IsInString("-",targetDate)) Then
targetDate = Split(targetDate,"-")
End If
' Lets see what we're working with here...
wscript.echo "Function DeployDate: targetDate(0) is " & targetDate(0)
wscript.echo "Function DeployDate: targetDate(1) is " & targetDate(1)
wscript.echo "Function DeployDate: targetDate(2) is " & targetDate(2)
Select Case Office
' Revisit: Wowzers - making some huge assumptions here: EU users use a Year Month Day format.
' Revisit: What about our folks in the US that use this format?
Case "Europe"
SerializedDate = DateSerial(targetDate(2),targetDate(1),targetDate(0))
If (Now() > SerializedDate) Then
wscript.echo "Function DeployDate: Now(" & Now() & ") is > SerializedDate(" & SerializedDate &")"
Else
wscript.echo "Function DeployDate: Now(" & Now() & ") is !> SerializedDate(" & SerializedDate &")"
End If
' Revisit: Another massive assumption here: US users use the 'normal' Day Month Year format.
' Revisit: What about those in the US that don't use this format?
Case Else
SerializedDate = DateSerial(targetDate(2),targetDate(0),targetDate(1))
If (Now() > SerializedDate) Then
wscript.echo "Function DeployDate: Now(" & Now() & ") is > SerializedDate(" & SerializedDate &")"
Else
wscript.echo "Function DeployDate: Now(" & Now() & ") is !> SerializedDate(" & SerializedDate &")"
End If
End Select
' If a time wasn't specified we'll just default to 1AM
' Time format rarely matters (read: i've never run into an incorrect time issue)
If (Len(targetTime) = 8) Then
targetTime = Split(targetTime,":")
SerializedTime = TimeSerial(targetTime(0),targetTime(1),targetTime(2))
wscript.echo "Function DeployDate: SerializedTime is " & SerializedTime
Else
SerializedTime = TimeSerial(01,00,00)
wscript.echo "Function DeployDate: SerializedTime is " & SerializedTime
End If
SerializedDT = SerializedDate & " " & SerializedTime
wscript.echo "Function DeployDate: SerializedDT is " & SerializedDT
wscript.echo "Function DeployDate: CDate(SerializedDT) is " & CDate(SerializedDT)
If (Now() > CDate(SerializedDT)) Then
wscript.echo "Now() is > CDate(SerializedDT)"
Else
wscript.echo "Now() is !> CDate(SerializedDT)"
End If
On Error GoTo 0
DeployDate = CDate(SerializedDT)
End FunctionHave I over complicated the situation?
HELP!!
Sincerely - Going bonkers!
