Skip to main content

Green hexagon with a white calendar and clock in the middle. DateTime Tool

One Tool Example

DateTime has a One Tool Example. Go to Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

Use DateTime to transform date-time data to and from a variety of formats, including both expression-friendly and human-readable formats. You can also specify the language of your date-time data. When carrying operations with 2 date-time data of different precision, the higher precision prevails. To format more precise date-time formats as strings, you need to insert a Select tool before you write to a database.

Important

Designer can't process dates prior to January 1, 1400.

Configure the Tool

Connect a data source to the input anchor of the DateTime tool. In the Configuration window, select the format to convert. There are 2 options:

  1. Date/Time format to string: Converts the date-time data format to a string.

    • Select the date/time field to convert: Select the dropdown to choose a date-time field (column). If no date-time columns are available, the field appears dimmed and reads No Suitable Input Fields Available.

    • Specify the new column name: Enter a new column name for the converted data, or use the default DateTime_Out.

    • Specify your DateTime Language: Select the dropdown to choose the language for your new string column output. Go to the Specify Your DateTime Language section below for a list of formats per language.

    • Select the format for the new column: Select the format for your new column from the list, or select Custom to create your own format via the Specify a custom format for the new column field. Refer to the Custom Format section below.

  2. String to Date/Time format: Converts string data to a DateTime format.

    • Select the string field to convert: Select the dropdown to choose a string field (column) to convert to a date-time format. If no string columns are available, the field appears dimmed and reads No Suitable Input Fields Available.

    • Specify the new column name: Enter a new column name for the converted data, or use the default DateTime_Out.

    • Specify your DateTime Language: Select the dropdown to identify the language of the incoming string data. Go to the Specify Your DateTime Language section below for a list of formats per language.

    • Select the format that matches the incoming string field: Select the format of the incoming string from the list, or select Custom to identify a different format via the Specify the format of the incoming string field section. Refer to the Custom Format section below.

Custom Format

You can specify a custom format that matches either your incoming string field or the new string field that is created.

  1. Select Custom to enter your own format. Designer displays an example based on the format you enter.

  2. Use the specifiers and separators described below (for example, dd, MM, yyyy), or the specifiers and separators supported by the DateTimeParse and DateTimeFormat DateTimefunctions (for example, %d, %m, %Y).

You can use an asterisk (*) as a wildcard character to replace any character in the date format field.

Day, Month, and Year Formats

  • d: Day of the month as digits, without leading zeros for single-digit days.

  • day: The full name of the day of the week.

  • dd: Day in 2 digits, with leading zeros for single-digit days. On input, leading zeros are optional.

  • dy: Day of the week as a 3-letter abbreviation. On input, full names are accepted but Alteryx doesn't check that the day of the week agrees with the rest of the date.

  • EEEE: The full name of the day of the week.

  • M: A single-digit month, without a leading zero.

  • MM: Month as digits, with leading zeros for single-digit months. On input, leading zeros are optional.

  • MMM: The abbreviated name of the month.

  • MMMM: The name of the month spelled out.

  • Mon: A 3-letter abbreviation of the name of the month. On input, full names are also accepted.

  • Month: Name of the Month. On input, abbreviations are also accepted.

  • yy: Year represented only by the last two digits. When converting from a string, two-digit years are mapped into the range from the current year, minus 66 years to the current year, plus 33 years. For example, in 2016, a two-digit year will be mapped into the range: 1950 to 2049. On input, four digits are also be accepted.

  • yyyy: Year represented by the full 4 digits. On input, 2 digits will also be accepted and mapped as done for the “yy” pattern.

Hour, Minute, and Second Format Specifiers

  • ahh: AM/PM (Simplified Chinese only).

  • H: Hour, with no leading zeros for single-digit hours (24-hour clock).

  • HH or hh: Hours, with leading zeros for single-digit hours (24-hour clock).

  • mm: Minutes, with leading zeros for single-digit minutes.

  • ss: Seconds, with leading zeros for single-digit seconds.

  • ffff: Precision, the number of 'f' characters means the number of digits to use for subseconds (example: fff for milliseconds, ffffff for microseconds, etc.).

Separators

On output, separators in the date/time format are used exactly. On input...

  • - and / are accepted as equivalent.

  • White space is ignored.

  • : and , must match exactly.

Specify Your DateTime Language

Date/Time Format to String

When you convert date-time data to string data, use the Specify your DateTime Language dropdown to choose the language for your new string column output. Depending on the language, different format options are available.

String to Date/Time Format

When you convert string data to date-time data, use the Specify your DateTime Language dropdown to identify the language of the incoming string data. Depending on the language, different format options are available.

English

  • yyyy-MM-dd hh:mm:ss

  • MM/dd/yyyy hh:mm:ss

  • MM/dd/yy hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • day, dd Month, yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd-MM.-yy

  • dd Month, yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • dy., Month dd, yyyy

  • MM-dd-yy

  • MM-dd-yyyy

  • MM/dd/yy

  • MM/dd/yyyy

  • Mon dd

  • Month dd, yyyy

  • Month, yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

简体中文 (Simplified Chinese)

  • yyyy'年'M'月'd'日' ahh'时'mm'分'ss'秒'

  • yyyy-MM-dd hh:mm:ss

  • yyyy'年'M'月'd'日'

  • yyyy'年'M'月'd'日'day

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

Français (French)

  • d Mon yy HH:mm:ss

  • yyyy-MM-dd hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • d Mon yy

  • d Month yyyy

  • day d Month yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

Deutsch (German)

  • dd.MM.yy HH:mm

  • dd.MM.yyyy HH:mm:ss

  • yyyy-MM-dd hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • dd.MM.yy

  • dd.MM.yyyy

  • d. Month yyyy

  • Day, dd. Month yyyy

  • Day, d. Month yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

Italiano (Italian)

  • dd/Mon/yy HH:mm:ss

  • yyyy-MM-dd hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/Mon/yy

  • dd Month yyyy

  • Day d Month yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

日本語 (Japanese)

  • yyyy-MM-dd hh:mm:ss

  • yyyy'年'M'月'd'日'day

  • yyyy'年'M'月'd'日'

  • M'月'd'日'

  • yyyy-MM-dd

  • yyyyMMdd

  • 'hh'時'mm'分'ss'秒'

  • HH:mm:ss

  • Custom

Português (Portuguese)

  • yyyyMMdd H.mm

  • yyyy-MM-dd hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • d' de 'Month' de 'yyyy

  • Day, d' de 'Month' de 'yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

Español (Spanish)

  • yyyyMMdd H.mm

  • yyyy-MM-dd hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • d' de 'Month' de 'yyyy

  • Day d' de 'Month' de 'yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd/MM/yy

  • dd/MM/yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

In this section: