WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: stanl on June 14, 2020, 03:34:44 AM

Title: FromOADate()
Post by: stanl on June 14, 2020, 03:34:44 AM
Ran into an issue with a script that removes unprintable characters from excel rows. Using clearformats() and dates become numeric offsets from 1/1/1900.  Discovered CLR and WB Timeformat() could address this.
Code (WINBATCH) Select


xlDate = 43214.6123
dt = ObjectClrNew("System.DateTime")
Message("Excel Date:":xlDate,TimeFormat(dt.FromOADate(xlDate),"MM/dd/yyyy hh:mm:ss t"))
Exit
Title: Re: FromOADate()
Post by: stanl on June 17, 2020, 03:28:18 AM
Here is a little more CLR Datetime functionality. WB is well furnished with Date/Time functions, so the script could be written without CLR:
Code (WINBATCH) Select


d = "2/16/2008 12:15:12 PM"
dt  = ObjectClrNew("System.DateTime")
dt  = dt.Parse(d)
TZ = ObjectClrNew("System.TimeZoneInfo")
TZID = TZ.Local
TUtc = TZ.ConvertTimeToUtc(dt)
Message("Time Zone: ":TZID.DisplayName,"String DateTime: ":d:@LF:"Converted: ":dt:@LF:"UTC: ":TUtc)


Title: Re: FromOADate()
Post by: kdmoyers on June 17, 2020, 10:34:47 AM
This is handy, thanks!
-K
Title: Re: FromOADate()
Post by: stanl on June 18, 2020, 02:41:02 AM
Quote from: kdmoyers on June 17, 2020, 10:34:47 AM
This is handy, thanks!
-K


Of course converting an historical timestamp to utc is flawed unless you calculate the bias based on whether the date falls within DST. For realtime conversion:
Code (WINBATCH) Select


dt1 = ObjectClrNew("System.DateTime")
dt2 = ObjectClrNew("System.DateTime")
dt1=dt1.Now  ;same as WB TimeYmdHm()
dt2=dt2.UtcNow
Message("Local: ":dt1,"UTC: ":dt2)