Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!
Free Trial

Alteryx Designer Desktop Discussions

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

Fixing DateTime Format

nwatzlaf
8 - Asteroid

I am trying to use a formula tool to just re-format my existing DateTime fields.

 

Right now the fields read YYYY-MM-DD HH:SS:MM

 

I am trying to get it to read MM-DD-YYYY HH:SS:MM

 

Can someone help me out with the proper syntax to achieve this. All my tried yield NULL values.

 

Thanks!!

 

 

4 REPLIES 4
caltang
17 - Castor
17 - Castor

I would suggest to use Alteryx date format and finish your workflow first, then change the date to a string format at the end so it outputs the way you want. 

Alteryx reads the date format as %Y-%m-%d. 

One way you can convert the date is to use DateTimeFormat([YourDate],”%m-%d-%Y”) to get your desired output. But make sure the data type is String and not date. Try it with the multi field formula tool to be sure.

Calvin Tang
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
nwatzlaf
8 - Asteroid

Thank you for your reply!

 

That is the formula I have tried - but I cannot get the syntax right to show the hours, mins, seconds after the date. .

MM/dd/yyyy hh:mm:ss

Is this not an acceptable DateTime format only a string format?

 

Thanks

CoG
14 - Magnetar

The Date and DateTime data types both utilize ISO 8601 format for consistency. As @caltang  mentioned, You would use the datetime data type until you reach the end of the workflow and are ready to format a text output for reporting purposes at which point you must create a formatted string field using the formula: 

DateTimeFormat([date],"%m-%d-%Y %H:%M:%S")

or 

DateTimeFormat([date],"%m/%d/%Y %H:%M:%S"), whichever you prefer.

 

Check out: DateTime Functions (alteryx.com) for all datetime formatting/parsing codes.

 

Happy Solving!

caltang
17 - Castor
17 - Castor

Yeah you can’t force the date to show as your format since it is fixed. If you already have a string and want to change to date then do your calculations THEN change to your date format (string data type), you’ll need to use DateTimeParse then DateTimeFormat. Maybe my initially message wasn’t worded right. 

@CoG is spot on. If you could provide some sample data, then it can be shown better. 

Calvin Tang
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
Labels
Top Solution Authors