Date Time Format in Alteryx

There are many data types that data analysts usually work with. String, number and date are mostly used in preparing the data. However, the date and time are more complicated than other data types because the field could be a String or Date. Depending on each cases, we would like to work with the date in a String format and sometimes we have to convert the String date to ISO date format in Alteryx.

In this blog, I will summarize some ways to convert a String date to ISO date format and in the reverse way in Alteryx.

Convert Date from String to ISO Date Format (YYYY-MM-DD)

In Alteryx, there are 2 ways to convert the string date to the ISO Date format.

**Image 1**: Text Input for Date Data in String data type

Assume that I have a Date Date column as Image 1 above, I would like to convert the String to a ISO Date Format. I checked Metadata to make sure my input is string data type. I could do it in 2 ways in Alteryx.

1/ Using DateTime tool in Parse Palette

**Image 2:** Using DateTime tool to convert String to Date

In Parse Palette, I drag the DateTime tool to the canvas. Then, I did (Image 2):

Choose the format to convert from String to Date/ Time format (2)
Select the column name that I would like to convert in "Select the string field to convert". In this case, I choose Date Date (3).
Put a new column name for output at "Specify the new column name". I put ISO Date Format. You can choose DateTime Language in the next drop down box (4).
Select the format that matched the Input column (Date Date). My input data is 03/02/2023. So my input format would be MM/dd/yyyy (5). Or you can choose custom with the format same as MM/dd/yyyy. Below is the table from Alteryx Documentation page (https://help.alteryx.com/20231/designer/datetime-tool) about specifiers for the incoming string fields. (Image 3)

**Image 3**: Specifiers and Description for DateTime tool

Then, I connect a Browse tool to the DateTime tool and run (Image 4).

**Image 4**: Result after converting a String to Date Time ISO format

2/ Using DateTimeParse() function in Formula tool

In this option, I use DateTimeParse(string, f, l) function in Formula Tool (where string is a Date String field, f is the input format and l is the specified language). DateTimeParse() function helps to convert a Date string to the ISO format (yyyy-mm-dd HH:MM:SS).

In the image 5 below, I summarized some popular specifiers for DateTimeParse() function which is used for the input Date String format. You can read the full list from Alteryx here: https://help.alteryx.com/20231/designer/datetime-functions

For the input format, I need to learn what Specifiers represent the input format. In this example, the input date is 03/02/2023 (where 03 is 2 digits of the month, 02 is 2 digits of the day, and 2023 is 4 digits of the year).

Based on the Specifiers table, my f input format would be: %m/%d/%Y . I don't have the time in Date Date field, so I don't need to put the Specifier for time format.

**Image 5**: Specifiers for DateTimeParse() function

Now, I will try it in Alteryx Designer.

**Image 6**: Convert a String date to ISO Date Format by Formula tool

Drag Formula tool to the canvas and connect with the Text Input tool (1).
Create a new column with the name ISO Date Format (2)
Type or Search the DateTimeParse function. Then input values we have to DateTimeParse function as: DateTimeParse([Date Date], "%m/%d/%Y"). The specified language l is optional, so I only use the input string ([Date Date]) and input format in quotation marks ("%m/%d/%Y"). (3)
Change data type to Date (4)

Then I use Ctrl + R to run the workflow. The result is a new column - ISO Date Format. Now, all the Date String values are converted to ISO format. If I check the metadata now, it shows Date type.

Convert ISO Date format (YYYY-MM-DD) to a String

1/ Using DateTime tool in Parse Palette

**Image 7:** Using DateTime tool to convert ISO Date Format to a String date

I have a Text Input with ISO Date Format in Date data type. In the Parse Palette, I drag the DateTime tool from Parse Palette to connect with the Text Input tool. Then, I did:

Choose Date/Time format to string (2)
Select the date/time to convert. In this case, I chose ISO Date Format field for the input (3)
Put a new column name in "Specify the new column name". I put the new column name is String Date (4)
Select Custom in Select the format for the new column (5)
I would like to show day of the week as a 3-letter abbreviation (Sun, Mon, Tue, …), then a 3-letter abbreviation of the month, 2 digits of the day and 4 digits of the year. (6) I could use the specifiers in Image 2 to apply. In this case, I will put: dy, MMM dd, yyyy
Alteryx will show the example and output (7)

Then run the workflow, I got the result as I want.

2/ Using DateTimeFormat() in Formula tool

**Image 8**: Using Formula tool to convert ISO date to a String date

Drag Formula tool to connect with the Input Text (1)
Create a new column String Date
Type DateTimeFormat([Date Time field], "format"). Date Time field that I would like to convert is ISO Date Format. The format is 3 letter of the day of the week, 3 letter of the Month, 2 digits of the day and 4 digits of the year. Therefore, my function would be: DateTimeFormat([ISO Date Format], "%a, %b %d, %Y") (3). You can use the table in Image 5 above.
The data type is V_WString by default, so I won't change it (4)

In Data Preview panel, I could see the preview after I typed the function. Then, I run the workflow. I also got the same result as using DateTime tool.

Pros and Cons for each option:

DateTime tool

Pros:

Easy to use and understand. Users only need to follow the instruction by choosing options in DateTime tool.
Users don't need to remember the specifiers much because Alteryx listed popular format in the box.

Cons:

Need to drag a new tool to the workflow to convert date time to the string or in the reverse way. It could increase the file size and affect the performance.
In compared with the Formula tool, there are many steps to do in the DateTime tool. It takes longer time than using the Formula tool.

Formula Tool

Pros:

Convenient if users are working with multiple formula in Formula tool. No need to use multiple tools.
Not many steps to convert the Date Time format to a String and in the reverse way. Only need to use 1 formula.

Cons:

Need to memorize the specifiers for the format of each function. Some specifiers are not consistency with the specifiers in Date Time as we have known. For example: %a for the abbreviation of the day of the week in DateTime functions in Formula tool. But in DateTime tool, dy is the specifier for the abbreviation of the day of the week.
Easy to confused between DateTimeFormat() and DateTimeParse() when users would like to convert an ISO date format to a string or in the reverse way. In DateTime tool, there are 2 radio options to choose.

In conclusion, depending on each case, users could choose a way to convert the date time into a string or in the reverse way. I hope this blog is helpful to you if you are working with Date and Time format in Alteryx.

See you in the next blog!

Author:

Le Luu

View Profile