We have a number of registry keys from various internal processes that put the date/timestamp as MM/DD/YYYY HH:MM:SS AM/PM or M/D/YYYY H:M:S AM/PM, and I have been tasked with converting those to YYYY-MM-DD HH:MM:SS (with hour being military / 24 hr format) when pulling back that data for analyses.
It ended up being waaay more complex than I thought. I was hoping I could use the format command, but I couldn’t get it to do what I wanted. And most forum posts seem to be converting from the BigFix timestamp format to something else, so they didn’t help much.
I also tried using regular expressions, but that just complicated things and didn’t really help since I still had to do the AM/PM -> military time conversion and leading zero fixes.
So here’s what I have so far - relevance gurus, please review to help me improve or simplify it.
q: ((/*year*/preceding text of first " " of following text of last "/" of it) & "-" & (/*month*/day_of_month (preceding text of first "/" of it) as two digits) & "-" & (/*day*/day_of_month (preceding text of first "/" of following text of first "/" of it) as two digits) & " " & (/*hour*/((if (it as lowercase ends with "pm") then (if ((it < 12)) then ((it + 12) as string) else (it as string) of it) of (preceding text of first ":" of it as integer) else (if (it as lowercase ends with "am") then (if ((it < 12)) then (day_of_month (it) as two digits) else ("00") of it) of (preceding text of first ":" of it as integer) else (it))) & (":" & /*minutes*/(if (length of it = 1 and it as integer < 10) then ("0" & it) else (it)) of (preceding text of first ":" of following text of first ":" of it) of it & ":" & /*second*/((if (length of it = 1 and it as integer < 10) then ("0" & it) else (it)) of it of (preceding text of first " " of following text of last ":" of it)) of it) of it) of following text of first " " of it)) of "5/09/2017 1:04:55 pm"
A: 2017-05-09 13:04:55
T: 0.388 ms
I: singular string
Thanks in advance!!