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

DateTime Tool

Version:
Current
Last modified: October 21, 2019

The DateTime tool transforms DateTime data to and from a variety of formats, including both expression-friendly and human readable formats.

Designer cannot process dates prior to January 1, 1400.

Tool configuration

Select the format to convert.

  1. Date/Time format to string: Converts DateTime data in the yyyy-MM-dd format or yyyy-MM-dd hh:mm:ss format to a string.
    • Select the date/time field to convert: Select from the list. The field is greyed out and reads "No Suitable Input Fields Available." if no DateTime columns are present.
    • Specify the new column name: Enter a new column name or use the default DateTime_Out.
    • Select the format for the new column: Select a format from the list, or select Custom to create your own format in Specify a custom format for the new column.
  2. String to Date/Time format: Converts string data to a DateTime format in the yyyy-MM-dd format or yyyy-MM-dd hh:mm:ss format.
    • Select the string field to convert: Select from the list. The field is greyed out and reads "No Suitable Input Fields Available." if no DateTime columns are present.
    • Specify the new column name: Enter a new column name or use the default DateTime_Out.
    • Select the format that matches the incoming string field: Select a format from the list, or select Custom to identify a different format in Specify the format of the incoming string field.

Custom formats

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

You can use the specifiers and separators as 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
  • 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 does not check that the day of the week agrees with the rest of the date.)
  • MM Month as digits with leading zeros for single digit months. (On input, leading zeros are optional.)
  • 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 will be mapped into the range from current year minus 66 years to the current year plus 33 years. So, in 2016, a two-digit year will be mapped into the range 1950 to 2049. (On input, four digits will 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
  • 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.
Was This Helpful?

Need something else? Visit the Alteryx Community or contact support.