Date Time Formatting Issue

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!

Date_time.yxzp

Date Time

Accepted answers

ShankerV

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

All comments

ShankerV

Hi @heidi-merritt

Please use the below formula.

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

Many thanks

Shanker V

binu_acs

@heidi-merritt One way of doing this

Date_time.yxmd

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.

Screenshot.PNG

Mitchell-Lutz Date_time.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703