Convert V_String Date/Time from WEEKDAY, MONTH DD, YEAR HH:MM format

ddiesel

What's the best approach to convert dates in the following format to Alteryx format?

DateTime

Friday, March 09, 2018 4:44 PM

Thursday, March 08, 2018 3:38 PM

Wednesday, March 07, 2018 10:45 PM

Tuesday, March 06, 2018 2:36 AM

Monday, March 05, 2018 9:07 PM

Right now I'm parsing by \s and then piecing it back together into YYYY-MM-DD HH:MM format after adding 12 hours to PM times.

I know there's a more sophisticated way... What's the best approach?

Common Use Cases

Best Practices

Accepted answers

MarqueeCrew

@ddiesel,

I'd like to see that the format %X would do the trick inside of a Parse, but here's how I would do it:

IF RIGHT([Date],2)="AM" THEN
DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M")
ELSEIF
RIGHT([Date],2)="PM" THEN
DateTimeAdd(DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M"),12,"hours")
Else
Null()
ENDIF

Cheers,

Mark

danrh

I'd probably add a condition in the formula tool to check if the hour is either noon or midnight and then re-adjusts the time:

DateTimeAdd([DateTime_Out],
IF Right([DateTime], 2)='PM'
THEN 12 ELSE 0 ENDIF +
Switch(DateTimeHour([DateTime_Out]),0,0,12,12,-12)
,'hours')

All comments

danrh

I would think you'd be able to do this in one step, but I can't seem to get it right. I'd use the DateTime tool to parse it initially, then a formula to add the 12 hours like you're already doing:

solution.yxmd

MarqueeCrew

@ddiesel,

I'd like to see that the format %X would do the trick inside of a Parse, but here's how I would do it:

IF RIGHT([Date],2)="AM" THEN
DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M")
ELSEIF
RIGHT([Date],2)="PM" THEN
DateTimeAdd(DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M"),12,"hours")
Else
Null()
ENDIF

Cheers,

Mark

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335