Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

ddiesel
13 - Pulsar
13 - Pulsar

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?

 

 

5 REPLIES 5
danrh
13 - Pulsar

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:

image.png

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ddiesel
13 - Pulsar
13 - Pulsar

Thanks @danrh and @MarqueeCrew

 

Both suggestions get me close and are more efficient than my 5 tool workaround. Now I just need to adjust for the 12:00 PM hour. 12:00 noon is reading as 12:00 midnight. I'm going to play around with it but reply back if you have a good solution. Thanks!

 

SentDateTime
Saturday, March 10, 2018 12:33 PM3/11/2018 0:33
Saturday, March 10, 2018 2:32 PM3/10/2018 14:32
Saturday, March 10, 2018 1:58 PM3/10/2018 13:58
Saturday, March 10, 2018 3:59 AM3/10/2018 3:59
Saturday, March 10, 2018 3:14 AM3/10/2018 3:14
Friday, March 09, 2018 12:58 PM3/10/2018 0:58
danrh
13 - Pulsar

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')

ddiesel
13 - Pulsar
13 - Pulsar

@danrh Thank you! I got it working yesterday with that same type of logic:

 

IF [DateTime3]="00" THEN DateTimeAdd([DateTime],-12,"hours") ELSE [DateTime] ENDIF

Again, your solution saves me a step though.

 

@danrh @MarqueeCrew ... thank you both! Between both your replies it's working well!

Labels