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

DateTime Tool

Version:
Current
Last modified: June 04, 2020

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.

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 of the incoming date-time data. See 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. See Custom Format 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 choose the language for the new date-time column. See 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. See Custom Format 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 DateTime functions (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 Format Specifiers
  • d: Day of the month as digits, without leading zeros for single-digit days.
  • d' de: Day of, typically followed by MM or MMMM (Portuguese, Spanish)
  • day: Day of the week spelled out.
  • dd: Day of the month as digits, with leading zeros for single-digit days. On input, leading zeros are optional.
  • dy: English 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: Day of the week spelled out (Japanese, Chinese Simplified).
  • M: A single month digit, without beginning with a 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 (French, Italian).
  • MMMM: The name of the month spelled out (German, French, Spanish, Portuguese, Italian).
  • Mon: A three-letter abbreviation of the English name of the month. On input, full names are also accepted.
  • Month: English 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 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 four digits. On input, two digits will also be accepted and mapped as done for the “yy” pattern.
Hour, Minute, and Second Format Specifiers
  • aah: AM/PM.
  • 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.
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

  • MM/dd/yyyy hh:mm:ss

  • MM/dd/yy hh:mm:ss

  • dd/MM/yyyy hh:mm:ss

  • dd/MM/yy hh:mm:ss

  • yyyy''M''d''

  • yyyy''M''d''EEEE

  • day, dd Month, yyyy

  • dd-MM-yy

  • dd-MM-yyyy

  • dd-Mon,-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

  • Month dd, yyyy

  • Month, yyyy

  • yyyy-MM-dd

  • yyyyMMdd

  • HH:mm:ss

  • Custom

Français (French)
  • d MMM yy HH:mm:ss
  • 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
  • d MMM yy
  • d MMMM yyyy
  • day d MMMM yyyy
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon.-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
Deutch (German)
  • dd.MM.yy HH:mm
  • dd.MM.yyyy HH:mm:ss
  • 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
  • dd.MM.yy
  • dd.MM.yyyy
  • d. MMMM yyyy
  • Day, dd. Month yyyy
  • Day, d. MMMM yyyy
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon-.-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
Italiano (Italian)
  • dd/MMM/yy HH:mm:ss
  • 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
  • dd/MMM/yy
  • dd MMMM yyyy
  • Day d MMMM yyyy
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon.-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
日本語 (Japanese)
  • 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
  • yyyy''M''d''EEEE
  • yyyy''M''d''
  • M''d''
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon.-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''
  • HH:mm:ss
  • Custom
Portugués (Portuguese)
  • yyyyMMdd H.mm
  • 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
  • d' de 'MMMM' de 'yyyy
  • Day, d' de 'MMMM' de 'yyyy
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon,-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
Español (Spanish)
  • yyyyMMdd H.mm
  • 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
  • d' de 'MMMM' de 'yyyy
  • Day d' de 'MMMM' de 'yyyy
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-Mon,-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
Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.