[How-To] Parse DateTime strings (PowerShell hybrid)

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

[How-To] Parse DateTime strings (PowerShell hybrid)

#1 Post by aGerman » 21 Feb 2021 07:02

Preface: Aacini provided the excelent 3rd party tools StdTime.exe and StdDate.exe which have similar functionality. If they meet your requirements and if performance matters, you will likely prefer using them:
viewtopic.php?f=3&t=3428
Another possibility is dbenham's jTimestamp.bat which is a JScript hybrid and supports custom output formatting and time zone offsets.
viewtopic.php?f=3&t=7523

The code below contains the ParseDateTime macro which wraps .NET functions in a little PowerShell code. The date and/or time strings to be converted are redirected to the standard input. The current date and time is assumed for an empty string or in case nothing is redirected. The output of the macro is a space-separated string with 11 tokens.
Define the 'culture' environment variable to change the locale settings used to parse the redirected string.
You are able to customize the code by adding specific format patterns.
For more information refer to the description in the code.

Note: Even if all examples in the code below are successfully converted for me, don't assume they can be converted for you as well. They contain localized German strings which are expected to fail on machines where German isn't the default culture. Replace them with the localized strings for your default language settings.

Code: Select all

@echo off &setlocal
:: https://www.dostips.com/forum/viewtopic.php?f=3&t=9971

:: The ParseDateTime macro reads a string from StdIn and tries to parse it as
::  DateTime value.
:: If an empty string is redirected or if the macro is executed without a
::  redirected string, the current date and time will be assumed.
:: If the macro succeeds to parse the string, it outputs 11 space-separated
::  values with fixed lengths:
:: yyyy MM dd HH mm ss fff n iii ww YYYY
::   yyyy  year
::   MM    month
::   dd    day
::   HH    hours (00 .. 24)
::   mm    minutes
::   ss    seconds
::   fff   fraction (milliseconds)
::   n     day of week (0 for Sunday .. 6 for Saturday)
::   iii   day of year (001 .. 365/366)
::   ww    ISO 8601 week of year
::   YYYY  year of the ISO 8601 calendar week
:: Strings containing a time zone offset are converted to local time.
:: Date values which are not specified in the input string are set to 1.
:: Time values are set to 0, respectively.
::
:: DMTF (CIM_DATETIME), ISO 8601, and RFC1123 formatted datetime strings are
::  supported. Long and short date and time strings are converted as long as
::  they meet the invariant or the local format.
:: Define variable 'culture' before calling the macro to use the format of a
::  different culture to parse the redirected string. E.g.:
::  set "culture=sv-SE"
::  This makes the macro use invariant or Swedish format.
::  For a list of possibly supported culture names refer to column 'Language
::  tag' in the related table:
::  https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/a9eac961-e77d-41a6-90a5-ce1a8b0cdb9c
:: Extra spaces in the converted string are automatically ignored.
:: For more information about standard formats refer to:
:: https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
::
:: The macro can be customized by specifying additional format strings. Right
::  now the list of custom formats in $cfo contains only one string for input
::  from the asctime C function (as used in ROBOCOPY). However, more format
::  strings can be appended, separated by a comma each. E.g.:
::  $cfo=[string[]]('ddd MMM d HH:mm:ss yyyy','HH:mm \h');^
:: For a list of custom format specifiers and syntax refer to:
:: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings
set ParseDateTime=powershell -nop -ep Bypass -c ^"$s=[string]$input;if(-Not $s){$dt=[DateTime]::Now}else{^
$cfo=[string[]]('ddd MMM d HH:mm:ss yyyy');^
$ic=[Globalization.CultureInfo]::InvariantCulture;^
$lc=if($env:culture){try{[Globalization.CultureInfo]$env:culture}catch{exit 1}}else{$null};^
try{$dt=[DateTime]::ParseExact($s,$cfo,$ic,15)}catch{^
try{$dt=[DateTime]::ParseExact($s,$cfo,$lc,15)}catch{^
try{$dt=[Management.ManagementDateTimeConverter]::ToDateTime($s)}catch{^
$s=$($s -replace '(\d),(\d)','$1.$2') -replace 'Z\b','+0:00';^
try{$dt=[DateTime]::Parse($s,$ic,143)}catch{^
try{$dt=[DateTime]::Parse($s,$lc,143)}catch{^
exit 1}}}}}}$dow=[int]$dt.DayOfWeek;$cw=$(Get-Culture).Calendar.GetWeekOfYear($(if($dow -match '[1-3]'){$dt.AddDays(3)}else{$dt}),2,1);^
$yow=if($dt.Month -eq 1 -and $cw -gt 51){$dt.Year-1}else{$dt.Year};^
($dt.toString('yyyy MM dd HH mm ss fff ')+$dow+$dt.DayOfYear.toString(' 000')+$cw.toString(' 00')+$yow.toString(' 0000'))^"

:: EXAMPLES:

echo *** no redirected string, leading zeros removed using SET /A ***
for /f "tokens=1-11" %%a in ('%ParseDateTime%') do set /a "year=1%%a-10000,mon=1%%b-100,day=1%%c-100,hh=1%%d-100,mm=1%%e-100,ss=1%%f-100,ms=1%%g-1000,dow=%%h,doy=1%%i-1000,woy=1%%j-100,yow=1%%k-10000"
echo year         %year%
echo month        %mon%
echo day          %day%
echo hours        %hh%
echo minutes      %mm%
echo seconds      %ss%
echo milliseconds %ms%
echo day of week  %dow%
echo day of year  %doy%
echo week of year %woy%
echo year of week %yow%

echo(
echo *** several tests, beginning with an empty string ***
for %%S in (
  ""                                         %= an empty string results in the output of the current date and time =%
  "19720126204745.800000+060"                %= DMTF formatted (as received from WMI) =%
  "26.01.1972 20:47:45,80"                   %= date and time variables, localized German output (comma as decimal separator) =%
  " 26. 01. 1972  20: 47: 45,80 "            %= date and time, localized German, extra spaces =%
  "26/01/1972 20:47:45.80"                   %= different date and decimal separators =%
  "26.01.1972"                               %= date only, localized German output =%
  "26.01.72"                                 %= date only, localized German, year with 2 digits =%
  "1972-01-26"                               %= different order and separator, ISO 8601 =%
  "Januar 1972"                              %= German month + year =%
  "January 1972"                             %= English month + year =%
  "20:47:45,80"                              %= time only, localized German =%
  "08:47:45.80 PM"                           %= time only, English =%
  "20:47:45"                                 %= time without fraction =%
  "20:47"                                    %= time without seconds =%
  "1972-01-26T20:47:45.8Z"                   %= RFC3339, ISO 8601 UTC notation =%
  "1972-01-26T20:47:45.8+1:00"               %= RFC3339, ISO 8601 with offset =%
  "Wed Jan 26 20:47:45 1972"                 %= C asctime()-like (as received from ROBOCOPY) =%
  "Mi Jan 26 20:47:45 1972"                  %= same but localized German =%
  "Wed 1972-Jan-26 20:47:45"                 %= as received from MAKECAB =%
  "Wed, 26 Jan 1972 20:47:45 GMT"            %= RFC1123 =%
  "Mittwoch, 26. Januar 1972 20:47:45"       %= long localized German =%
  "January, 26 1972 20:47:45"                %= long English =%
  "Wednesday, January 26, 1972 8:47:45.8 PM" %= another long English =%
) do (
  REM Note that the surrounding quotes are removed for the redirection.
  echo(%%~S
  for /f "tokens=1-11" %%a in ('echo(%%~S^|%ParseDateTime%') do echo -^> y:%%a M:%%b d:%%c H:%%d m:%%e s:%%f ms:%%g DoW:%%h DoY:%%i WoY:%%j YoW:%%k
  echo(
)

echo(
echo *** use Swedish culture settings for a localized string ***
set "culture=sv-SE"
set "S=den 26 januari 1972 20:47:45"
echo(%S%
for /f "tokens=1-11" %%a in ('echo(%S%^|%ParseDateTime%') do echo -^> y:%%a M:%%b d:%%c H:%%d m:%%e s:%%f ms:%%g DoW:%%h DoY:%%i WoY:%%j YoW:%%k
echo(
pause


Output on my machine:

Code: Select all

*** no redirected string, leading zeros removed using SET /A ***
year         2021
month        2
day          26
hours        18
minutes      3
seconds      2
milliseconds 978
day of week  5
day of year  57
week of year 8
year of week 2021

*** several tests, beginning with an empty string ***

-> y:2021 M:02 d:26 H:18 m:03 s:03 ms:260 DoW:5 DoY:057 WoY:08 YoW:2021

19720126204745.800000+060
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

26.01.1972 20:47:45,80
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

 26. 01. 1972  20: 47: 45,80
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

26/01/1972 20:47:45.80
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

26.01.1972
-> y:1972 M:01 d:26 H:00 m:00 s:00 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

26.01.72
-> y:1972 M:01 d:26 H:00 m:00 s:00 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

1972-01-26
-> y:1972 M:01 d:26 H:00 m:00 s:00 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Januar 1972
-> y:1972 M:01 d:01 H:00 m:00 s:00 ms:000 DoW:6 DoY:001 WoY:52 YoW:1971

January 1972
-> y:1972 M:01 d:01 H:00 m:00 s:00 ms:000 DoW:6 DoY:001 WoY:52 YoW:1971

20:47:45,80
-> y:0001 M:01 d:01 H:20 m:47 s:45 ms:800 DoW:1 DoY:001 WoY:01 YoW:0001

08:47:45.80 PM
-> y:0001 M:01 d:01 H:20 m:47 s:45 ms:800 DoW:1 DoY:001 WoY:01 YoW:0001

20:47:45
-> y:0001 M:01 d:01 H:20 m:47 s:45 ms:000 DoW:1 DoY:001 WoY:01 YoW:0001

20:47
-> y:0001 M:01 d:01 H:20 m:47 s:00 ms:000 DoW:1 DoY:001 WoY:01 YoW:0001

1972-01-26T20:47:45.8Z
-> y:1972 M:01 d:26 H:21 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

1972-01-26T20:47:45.8+1:00
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972

Wed Jan 26 20:47:45 1972
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Mi Jan 26 20:47:45 1972
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Wed 1972-Jan-26 20:47:45
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Wed, 26 Jan 1972 20:47:45 GMT
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Mittwoch, 26. Januar 1972 20:47:45
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

January, 26 1972 20:47:45
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Wednesday, January 26, 1972 8:47:45.8 PM
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:800 DoW:3 DoY:026 WoY:04 YoW:1972


*** use Swedish culture settings for a localized string ***
den 26 januari 1972 20:47:45
-> y:1972 M:01 d:26 H:20 m:47 s:45 ms:000 DoW:3 DoY:026 WoY:04 YoW:1972

Drücken Sie eine beliebige Taste . . .
Some information about the magic numbers in the code for those who are wondering ...
The 15 passed to the 4th parameter of ParseExact and the 143 passed to the 3rd parameter of Parse are DateTimeStyles AllowWhiteSpaces + NoCurrentDateDefault (+ RoundtripKind).
The 2 passed to the 2nd parameter of GetWeekOfYear is the CalendarWeekRule FirstFourDayWeek, the 1 passed to the 3rd parameter is the DayOfWeek Monday.


Steffen
Last edited by aGerman on 27 Feb 2021 18:17, edited 6 times in total.
Reason: parse time zone offset reliably

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#2 Post by Squashman » 21 Feb 2021 18:33

I will definitely use this. Day of year (52) would be a nice feature as well.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#3 Post by aGerman » 22 Feb 2021 06:01

Good call! Updated :wink:

Steffen

misol101
Posts: 475
Joined: 02 May 2016 18:20

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#4 Post by misol101 » 22 Feb 2021 06:38

Nice, can you add week number as well? :wink: Don't know about other countries but in Sweden we are obsessed with referring to the current week number (in the workplace etc)

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#5 Post by aGerman » 22 Feb 2021 08:31

Even if the MS methods aim to be ISO 8601 compliant, they in fact don't. This would need some further investigation. If I have to roll own code to calculate it, I'm not sure if the macro length will explode :lol: I'll try to include it but don't want to promise yet :wink:

Steffen

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#6 Post by aGerman » 22 Feb 2021 15:22

ISO 8601 calendar week is the tenth field now.

Steffen

misol101
Posts: 475
Joined: 02 May 2016 18:20

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#7 Post by misol101 » 23 Feb 2021 02:58

Sweet 8)

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#8 Post by aGerman » 25 Feb 2021 12:13

I shortened the macro length. Because some field names of enumerations are replaced with their integer values, I added a paragraph about their meaning.

Steffen

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#9 Post by aGerman » 25 Feb 2021 15:17

Another feature:
I enabled the possibility to use a different locale setting rather than your default. Since we can't pass arguments to the macro, environment variable culture can be used. Define it with a language tag (like en-US) before the macro is called. If this variable is undefined, the current default will be used like before.

Steffen

dbenham
Expert
Posts: 2461
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#10 Post by dbenham » 25 Feb 2021 15:44

The documentation should specify that the output always uses the local time zone. The offset from UTC should be added to the output, preferably as minutes offset. Not all time zones are offset by an integral hour.

I was surprised the custom format string works for input. I thought it was only used for output.

My machine is configured for US date formats (MM/dd/yyyy instead of dd/MM/yyyy)

Do you know if there is some way to get the routine to support both?

I tried adding '}dd.MM.yyyy' to $cfo to support the European dd.MM.yyyy format with a triggering } character.
It did not work.


It might be useful to compare results with jTimestamp.bat.
The equivalent jTimeStamp call is below, though the dateTime input strings that JScript supports are different than what .Net supports.

Code: Select all

jTimeStamp -d "'InputString'" -f "{yyyy} {mm} {dd} {hh} {nn} {ss} {fff} {isowd} {dy} {isowk}"
jTimeStamp also supports many other types of input such as array of values and milliseconds since 1970-01-01 00:00:00 UTC. It also supports data/time computations, and you can format the output anyway you want.


Dave Benham

dbenham
Expert
Posts: 2461
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#11 Post by dbenham » 25 Feb 2021 16:14

If you include the ISO 8601 week number, then you should also add the week year. The week year and calendar year do not always match when dealing with a date within a week of january 1.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#12 Post by aGerman » 25 Feb 2021 16:20

dbenham wrote:
25 Feb 2021 15:44
The documentation should specify that the output always uses the local time zone.
Agreed. I'm struggling with the correct wording because any specified offset is simply ignored. So, actually nothing is converted to the local time zone.
EDIT: Wrong. Only the Z at the end of an ISO string has been ignored at that time. Time zone offsets are now used to convert to local time.

dbenham wrote:
25 Feb 2021 15:44
The offset from UTC should be added to the output
I thought about it but didn't get the 'zzz' specifier to work reliably.

dbenham wrote:
25 Feb 2021 15:44
Do you know if there is some way to get the routine to support both?
Think about something ambiguous like 12/01/2020. You may use the culture variable to control the way it is parsed. But I can't think of a way to support different orders of month and day at the same time.

dbenham wrote:
25 Feb 2021 15:44
I tried adding '}dd.MM.yyyy' to $cfo to support the European dd.MM.yyyy format with a triggering } character.
It did not work.
That's weird. You know for me it would be the other way around. So specifying ...

Code: Select all

$cfo=[string[]]('ddd MMM d HH:mm:ss yyyy','}MM/dd/yyyy');^
... and running it like that ...

Code: Select all

echo(}12/01/2020|%ParseDateTime%
... yields ...

Code: Select all

2020 12 01 00 00 00 000 2 336 49
... which is correct in this case.
Since ParseExact is always tried first, custom formats have higher priority.

dbenham wrote:
25 Feb 2021 16:14
If you include the ISO 8601 week number, then you should also add the week year. The week year and calendar year do not always match when dealing with a date within a week of january 1.
Right. Will do that in the next update.

Steffen

dbenham
Expert
Posts: 2461
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#13 Post by dbenham » 25 Feb 2021 22:26

aGerman wrote:
25 Feb 2021 16:20
Since ParseExact is always tried first, custom formats have higher priority.
Ah, cool. I assumed it would be in reverse. I see how it works now.

I also missed the culture variable option until just now.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#14 Post by aGerman » 26 Feb 2021 11:14

Year of the ISO week is the 11th token now. Obviously this will not be a replica of jTimestamp, Dave. I linked it in the preface. Feel free to update the initial post with some more advantages of jTimestamp which stand out :)

Steffen

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: [How-To] Parse DateTime strings (PowerShell hybrid)

#15 Post by aGerman » 27 Feb 2021 18:22

dbenham wrote:
25 Feb 2021 15:44
the output always uses the local time zone
This is true now. The 'Z' in the ISO 8601 UTC notation was previously ignored.

Steffen

Post Reply