Fixing DateTime Format

Question

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 · Answer

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.

CoG · Answer

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!

nwatzlaf · Answer

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