Date Time Formatting Issue

[heidi-merritt]

Hello!

I'm trying to convert a string date that's in yyyy-dd-MM hh:mm:ss format to mm/dd/yyyy.

Per another post in community, I started by using the DateTime tool to convert the string to date/time, then used the DateTimeFormat formula to put into mm/dd/yyyy format. This is not working for me and I've made several tweaks to try and get it to work. Am I missing something?

I really struggle with dates and times in Alteryx so any help is appreciated. 

I have attached a packaged workflow.

Thank you!

[ShankerV · Accepted solution]

Hi @heidi-merritt 

Please use the below formula.

After your summarize tool, use the formula tool with below formula.

IF contains(LEFT([Reservation Creation Date], 3),"-")
THEN DateTimeFormat(datetimeparse([Reservation Creation Date],"%d-%m-%y %H:%M:%S"),"%m/%d/%Y")
ELSE DateTimeFormat(datetimeparse([Reservation Creation Date],"%y-%d-%m %H:%M:%S"),"%m/%d/%Y")
ENDIF

Many thanks

Shanker V

[binuacs]

@heidi-merritt One way of doing this

[OllieClarke]

@heidi-merritt just to add to @ShankerV's solution - are you 100% sure that the incoming format is yyyy-dd-MM hh:mm:ss? I've never seen a date go 2023-27-07 before, so I'd be surprised if this is the case.

If you meant yyyy-MM-dd hh:mm:ss, then the formula should be

IF contains(LEFT([Reservation Creation Date], 3),"-")
THEN DateTimeFormat(datetimeparse([Reservation Creation Date],"%d-%m-%Y %H:%M:%S"),"%m/%d/%Y")
ELSE DateTimeFormat(datetimeparse([Reservation Creation Date],"%Y-%m-%d %H:%M:%S"),"%m/%d/%Y")
ENDIF

Also the datatype of the RSV date field should be a string, not a date as you are formatting a string, rather than parsing a date.

Ollie

[MichelleMitchellLutz]

Hi! A couple of key points:

• To use the Date/Time tool, all dates within the field have to have the same syntax (yyyy-mm-dd OR dd-mm-yyyy). I used Regex to split them 
  • You'll need to repeat this process for each date field
• If you use the DateTimeFormat formula, you'll need to make the date type string (or some variation of string) 

See attached workflow and let me know if you need additional assistance. :)

[cjaneczko]

The issue with the formula is the Data Type needs to be string based on how you have it written. See notes below.

https://help.alteryx.com/11.7/Reference/DateTimeFunctions.htm

[heidi-merritt]

Thank you!