Need VBS version of Excel date conversion

NVIT
NVIT used Ask the Experts™
on
I have this in Excel but need a vbscript version to run on CMD line, i.e. cscript dateconvert.vbs 1255635089

A5=1255635089
B5=A5/86400+DATE(1970,1,1)-(10/24)

I have this so far...

BTW, I'm not an expert VBS coder as you can see. So, I'm open to better / cleaner solution.

EpochToDate.vbs
Set objArgs = WScript.Arguments
nEpochVal = objArgs(0)
Dim dblVbEpoch
    dblVbEpoch = CDbl(DateAdd("s", nEpochVal, #1970/1/1#))

sTime=FormatDateTime(DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch)),3)
sTime=Replace(sTime,":","_")
sTime=Replace(sTime," ","")
WScript.Echo myDateFormat(DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch))) & " " & sTime

Function CurrentTZOffset()
    With CreateObject("WScript.Shell") 
        CurrentTZOffset = - .RegRead( _ 
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Function myDateFormat(myDate)
    d = WhatEver(Day(myDate))
    m = WhatEver(Month(myDate))    
    y = Year(myDate)
    myDateFormat= y & "_" & m & "_" & d
End Function

Function WhatEver(num)
    If(Len(num)=1) Then
        WhatEver="0"&num
    Else
        WhatEver=num
    End If
End Function

Open in new window


But, running cscript /nologo EpochToDate.vbs 1255635089
... echos it as: 2009_10_15 9_31_29AM
... which is fine except:

- I don't want the AM or PM suffix
- If the hour is a single digit, I'd like it prefixed with a zero. This, for sorting reasons.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
David Johnson, CD
Top Expert 2016

Commented: 
Set fso = CreateObject ("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
nEpochVal = objArgs(0)
Dim dblVbEpoch
Dim Mydate
dblVbEpoch = CDbl(DateAdd("s", nEpochVal, #1970/1/1#))
Mydate = DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch))
Mydate = Replace(Mydate,"-","_")
Mydate = Replace(Mydate," ","_")
Mydate = Replace(Mydate,":","_")
WScript.echo(Mydate)

Function CurrentTZOffset()
    With CreateObject("WScript.Shell") 
        CurrentTZOffset = - .RegRead( _ 
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Open in new window


08_Dec_2009_6_17_30_PM  (I am in EDT5EST and my regional settings are set to dd-MMM-YYYY
NVITEnd-user support

Author

Commented:
David,

I haven't had a chance to try your solution. I assume your result 08_Dec_2009_6_17_30_PM is based on it.

Still...
- I don't want the AM or PM suffix.
- If the hour is a single digit, I'd like it prefixed with a zero.

To clarify, 2009_12_08_06_17_30_PM should show (localized) as 2009_12_08_18_17_30
Test your restores, not your backups...
Top Expert 2016
Commented:
How about this:

If (Wscript.Arguments.Count > 0) Then
  nEpochVal = Wscript.Arguments(0)
Else
  WScript.Echo "ERROR : No input value specified."
  WScript.Quit -1
End If

dblVbEpoch = CDbl(DateAdd("s", nEpochVal, #1970/1/1#))

WScript.Echo myDateTimeFormat(DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch)))

Function CurrentTZOffset()
    With CreateObject("WScript.Shell") 
        CurrentTZOffset = - .RegRead( _ 
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Function myDateTimeFormat(myDate)
    myDateTimeFormat= Year(myDate) & "_" & _
                      ZPad(Month(myDate), 2) & "_" & _
                      ZPad(Day(myDate), 2) & " " & _
                      ZPad(Hour(myDate), 2) & "_" & _
                      ZPad(Minute(myDate), 2) & "_" & _
                      ZPad(Second(myDate), 2)
End Function

Function ZPad(strText, intLen)
   ' Pad with leading zeros on the left to specified length
   ZPad = Right(String(intLen, "0") & strText, intLen)
End Function

Open in new window

~bp
NVITEnd-user support

Author

Commented:
Thanks, Bill!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial