Fixing DateTime Format

[nwatzlaf]

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!!

[caltang]

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.

[nwatzlaf]

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]

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]

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.