DateTime Tool
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:
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.
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.
Select Custom to enter your own format. Designer displays an example based on the format you enter.
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.
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.
Limitation with 6-Digit Dates
Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can...
- Use 4 digits for the year (for example, 2017 instead of 17), depending on your range of dates.
- Use the RegEx tool to insert a space after the first 2 digits in the string.
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.).
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.
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
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
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
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
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
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
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
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