In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Date Time Formatting Issue

heidi-merritt
5 - Atom

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!

 

 

6 REPLIES 6
ShankerV
17 - Castor

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
21 - Polaris

@heidi-merritt One way of doing this

image.png

OllieClarke
15 - Aurora
15 - Aurora

@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

 

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
13 - Pulsar

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

 

image.png

heidi-merritt
5 - Atom

Thank you!

Labels
Top Solution Authors