Transforming long dates to date format

[akasubi]

I currently have data with the date field formatted as "September 15, 2019" as an example.

Is there a tool that can automatically transform this into a date format that follows dd-mm-yyyy?

Many thanks

afk

[JoshuaGostick · Accepted solution]

Hi @akasubi,

You can use the DateTime tool found under the Parse section of tools and enter the following date format:

Month dd, yyyy

This will convert that date into a date type field.

Hope this helps!

Thanks,

Josh

[jrgo]

DATETIMEPARSE([FIELD], '%b %d, %Y)

[CharlieS · Accepted solution]

There are two steps that are necessary here:

1. Parse the input value into the standard date format (ISO 8601) which is YYYY-MM-DD. This is the only official date format that Alteryx supports for datetime calculations (all other formats are simply string fields).

This can be achieved with the following expression:

DateTimeParse([Input],"%B %d,%Y")

2. Take this date field and output as a string in the DD-MM-YYYY format you requested:

DateTimeFormat([DateField],"%d-%m-%Y")

You can combine both of these steps into one expression to achieve your desired result in one formula:

DateTimeFormat(DateTimeParse([Input],"%B %d,%Y"),"%d-%m-%Y")

[akasubi]

Thanks for this @CharlieS 

Can you explain what the syntax '%B %d,%' means? i just want to understand how the formula works.

[akasubi]

Thanks for this @JoshuaGostick 

Would just like to add for others who may have the same query, you first select 'Custom' in the list of incoming string fields, and then in the box under it, you specify the format Month dd, yyyy (or whatever it is in your case)

[CharlieS]

Sure thing!

In that part of the expression, you are parsing the input into the input string into the standard date format. The DateTimeParse( function needs a string to parse ("September 15, 2019") and a argument to use how to extract the date information from that ("%B %d, %Y"). Using the documentation link below: The result of this part of the expression is "2019-11-15".

%B is the full month name ("September")

%d is the day of month ("15")

%Y is the 4 digit year ("2019")

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