DateTime Functions
A DateTime function performs an action or calculation on a date and time value. Use a DateTime function to add or subtract intervals, find the current date, find the first or last day of the month, extract a component of a DateTime value, or convert a value to a different format.
Alteryx uses the format YYYY-MM-DD HH:MM:SS to represent dates and times. For additional accuracy (up to 18 digits of precision) add a period and additional digits after the seconds position. For example, YYYY-MM-DD HH:MM:SS.fff. If a DateTime value is not in this format, Alteryx reads it as a string. To convert a column for use and manipulation in the DateTime format, use the DateTimeParse function in the expression editor or the DateTime tool.
Date Format
Some DateTime functions require you to set the date's format. Format strings are comprised of specifiers and separators.
DateTime Functions
Refer to the DateTime functions below.
DateTimeAdd
DateTimeAdd(dt,i,u): Adds a specific interval to a date-time value.
Parameters
dt: Date-time data.
i: Positive or negative integer of time to add or subtract.
u: Date-time unit, specified between quotes using keywords listed in the Keywords for Date-Time Units section.
Example
DateTimeAdd(DateTimeToday(), -1, "days") returns yesterday’s date.
DateTimeAdd(DateTimeFirstOfMonth(), 1, "months") returns the first of the next month.
DateTimeAdd("2016-01-30", 1, "month") returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th).
DateTimeAdd("2016-03-30", -1, "month") returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th).
DateTimeAdd("2016-03-30 01:02:03.000000", 495, "microseconds") returns 2016-03-30 01:02:03.000495.
Read More
Any fraction in the duration is truncated. For example, you cannot add "1.5 hours". Instead, add "90 minutes".
Adding a unit does not change the value of smaller units. For example, adding hours does not change the value of minutes or seconds. Adding months does not change the day or time unless the resulting month does not have such a day. In that case, it goes to the last day of that month.
This function performs calculations as if the time zone does not have time changes related to Daylight and Standard time. This might produce unexpected results if your calculation includes dates and times when the time changes from Daylight to Standard or vice versa. If the precision of the actual time that passes is critical, you should first convert your data to UTC.
DateTimeDay
DateTimeDay(dt): Returns the numeric value for the day of the month in a date-time value.
Parameters
dt: Date-time data.
Example
DateTimeDay("2017-03-24 11:43:23") returns 24.
DateTimeDiff
DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first (dt1 – dt 2) and returns the difference as an integer. The duration is returned as a number, not a string, in the specified time units.
Parameters
dt: Date-time data.
u: Date-time unit, specified between quotes using keywords from the Keywords for Date-Time Units section.
Example
DateTimeDiff("2024-01-18", "2024-01-15", "days") returns 3.
DateTimeDiff("2016-02-15 00:00:00", "2016-01-15 00:00:01", "Months") returns 1 (because the start and end are the same day of the month).
DateTimeDiff("2012-02-29","2011-03-01","years") returns 0 (even though 2012-02-29 is 365 days after 2011-03-01, February 29 is before March 1st, so "one year" has not yet been completed).
DateTimeDiff("2016-02-14", "2016-01-15", "Months") returns 0 (because the day in February is less than the day in January).
DateTimeDiff("2016-02-14 23:59:59", "2016-01-15 00:00:00", "Months") returns 0 (even though it is only one second short of reaching the required day).
DateTimeDiff('2017-02-28', '2016-02-29', 'Months') returns 11 (even though the 28th is the last day of February in 2017, the 28 is less than 29).
DateTimeDiff('2017-02-28 00:59:59.28139502', '2017-02-28 00:59:59.12383125', 'msecs') returns 157 (for milliseconds between date-time values).
Read More
For Month and Year differences, a month is only counted when the end day reaches the start day, ignoring the time of day.
For precision of Day, Hour, Minute, and Second, the result is calculated precisely, then fractional parts are truncated, not rounded. Therefore...
DateTimeDiff(‘2016-01-01 00:59:59’, ‘2016-01-01 00:00:00’, ‘Hours’)is zero.DateTimeDiff(‘2016-01-01 23:59:59’, ‘2016-01-01 00:00:00’, ‘Days’)is zero.
Precision names can be shortened to their first three characters (like 'sec' and 'min'), case is insensitive.
Be careful when storing time differences in seconds. An Int32 can only hold a difference of 68 years in seconds or 4082 years in minutes. You can use a Double or an Int64 to hold intervals between all supported dates.
This function performs calculations as if the time zone does not have time changes related to Daylight and Standard time. This might produce unexpected results if your calculation includes dates and times when the time changes from Daylight to Standard or vice versa. If the precision of the actual time that passes is critical, you should first convert your data to UTC.
DateTimeFirstOfMonth
DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.
DateTimeFormat
DateTimeFormat(dt,f,[l],[tz]): Converts date-time data from ISO format to another specified format (f), in a specified language (l), using the time zone name (tz) for use by another application. Output to String data type. If the specified format carries less precision than the incoming data, the output is truncated and not rounded. For example, if you pass in a time to microseconds (.012789) and the format specifies the time to milliseconds, the result is truncated to .012 rather than rounded to .013. The optional time zone parameter lets you control the style of the text that is added to the string (for example, 'EDT').
Parameters
dt: Date-time data.
f: The format to which to convert the data, expressed in a format string. Refer to the Specifiers section below.
l: This is an optional language parameter. It defaults to your selected Designer language. For example, if Designer is set to French, the function reads DateTimeParse(dt,f,"Français") by default. Note that if you omit this parameter, the function uses English regardless of your Designer interface language. Go to Language Parameters for a list of accepted language values.
tz: This is an optional time zone parameter. Note that the data is not converted. This is simply to guide the style of the text added to the string.
The time zone identifier determines what offset to show if you use the offset format instruction (%z) or to get a name for the time zone to show if you use the time zone name format instruction (%Z).
The time zone name preserves the style of the argument: short (like EST), long (like Eastern Standard Time), generic (like ET), or specific (like EST/EDT).
The name is modified to match the correct season for the data. For example, if the tz is provided as EST for 2024-08-15, it is corrected to EDT. Similarly, for 2024-01-15, if the tz is provided as EDT, it is corrected to EST. For both of these examples, if the tz is provided as a generic ET it is not changed to show whether it is Daylight or Standard time.
If the tz is not provided in the function call, the system time zone is used.
Example
DateTimeFormat('2008-04-22',"%d-%m-%Y") returns 22-04-2008 for the date April 22, 2008 (ISO format: 2008-04-22).
DateTimeFormat([DateTime_Out],"%A","Spanish") returns "martes" for the ISO date 2020-07-14 (where July 14th is a Tuesday).
DateTimeFormat('11:59:57.890994389','%H:%M:%S.%5') returns '11:59:57.89099' (Truncates to 5 digits of precision based on digits specified).
DateTimeFormat('11:59:57.99','%H:%M:%S,%3') returns '11:59:57,990' (You can get your locale’s decimal separator. This example uses a comma instead of a decimal point).
DateTimeFormat('2024-03-10 01:00:00', '%b %d, %y %T %Z', '', 'ET') returns 'Mar 10, 24 01:00:00 ET'. The style of the time zone argument is short and generic, so that is used in the result.
DateTimeFormat('2024-03-10 01:00:00', '%b %d, %y %T %Z', '', 'EDT') returns 'Mar 10, 24 01:00:00 EST'. The style of the time zone argument is short and specific. It's incorrect for the time of the data. The result gets the specific time zone offset corrected.
DateTimeFormat('2024-03-10 01:00:00', '%b %d, %y %T %Z', '', 'Eastern Time') returns 'Mar 10, 24 01:00:00 Eastern Time’. The style is kept, and no shift needs to be corrected.
DateTimeFormat('2024-03-10 01:00:00', '%b %d, %y %T %Z', '', 'Eastern Daylight Time') returns 'Mar 10, 24 01:00:00 Eastern Standard Time’. The time zone shift is corrected.
DateTimeFormat('2024-03-10 01:00:00', '%b %d, %y %T %z', '', 'ET') returns 'Mar 10, 24 01:00:00 -0400’.
DateTimeHour
DateTimeHour(dt): Returns the hour portion of the time in a date-time value.
Parameters
dt: Date-time data.
Example
DateTimeHour("2017-03-24 11:43:23") returns 11.
DateTimeHour("2017-03-24") returns 0, as midnight is the assumed hour when no time is specified with a date.
DateTimeLastOfMonth
DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
Designer uses the date and time when the workflow starts running. In a batch process, this time is used with each new set of data. This allows for consistency if the process takes a long time.
DateTimeMinutes
DateTimeMinutes(dt): Returns the minutes portion of the time in a date-time value.
Parameters
dt: DateTime data.
Example
DateTimeMinutes("2017-03-24 11:43:23") returns 43.
DateTimeMonth
DateTimeMonth(dt): Returns the numeric value for the month in a date-time value.
Parameters
dt: Date-time data.
Example
DateTimeMonth("2017-03-24 11:43:23") returns 3.
DateTimeMonth("11:43:23") returns [Null], because the incoming data is not valid.
DateTimeNow
DateTimeNow([tz]): Returns the current date and time, including seconds. If you provide the optional time zone (tz) parameter, the returned time is in that time zone. If you don't provide a time zone, the returned time uses your system time zone.
Parameters
tz: (Optional) Provide a time zone for the returned time. If provided, the returned time uses that time zone. If omitted, the returned time uses your system time zone.
DateTimeNowPrecise
DateTimeNowPrecise(digits,[tz]): Returns the current system date and time with fractions of a second (up to 18 digits of precision). If you provide the optional time zone (tz) parameter, the returned time is in that time zone. If you don't provide a time zone, the returned time uses your system time zone.
Note that if you request precision beyond what your operating system provides, the function fills in the remainder with zeros, like in the second example below.
Parameters
digits: Specify the precision of the returned value, between 0 and 18 (inclusive).
tz: (Optional) Provide a time zone for the returned time. If provided, the returned time uses that time zone. If omitted, the returned time uses your system time zone.
Example
DateTimeNowPrecise(3) returns '2022-10-14 11:59:57.991'.
DateTimeNowPrecise(12) returns '2022-10-14 11:59:57.991238400000'.
DateTimeParse
DateTimeParse(string,f,[l],[tzName]): Converts a date string (string) with the specified format (f), in a specified language (l), with a specified time zone (tzName) to the format (YYYY-MM-DD HH:MM:SS with optional date-time precision if applicable). If the specified format carries less precision, the output is truncated.
Parameters
string: Date-time string data. The incoming data must be a String data type and can be in any format of date-time as long as this format agrees with the format you specify for the f parameter.
f: The format of the incoming date string data that you are converting, expressed in a format string between quotes.
Time Zones
If the incoming format includes %z or %Z, Alteryx knows the input time zone. It converts the DateTime result value into the local or specified time zone. If the format or data does not have time zone information, Alteryx assumes the input is already in the local time zone, and does not apply any time shifts.
Review your data and take appropriate steps to ensure that your output is in the format you expect. For important time zone considerations, go to Time Zone and Daylight/Standard Time Changes.
l: (Optional) The language of the incoming date string data that you are converting. The language parameter defaults to your selected Designer language when editing formulas. If omitted or blank, "English" is used. For example, if Designer is set to French, the function reads DateTimeParse(string,f,"Français") by default. Go to Language Parameters for accepted language values.
tz: (Optional) The time zone to convert the data to if %z or %Z is in the format and the text has the name of a time zone or an offset in it. The examples will help to clarify this. If the time zone is omitted, the system time zone is used for conversion.
Example
DateTimeParse("2016/28-03","%Y/%d-%m") returns 2016-03-28.
DateTimeParse('mardi 14 juillet 2020', '%A %d %B %Y', 'Français') returns 2020-07-14 where the incoming date string is "mardi 14 juillet 2020" (Tuesday 14 July 2020).
DateTimeParse('Oct 4, 22 11:59:57.99', '%b %d, %y %H:%M:%S') returns '2022-10-04 11:59:57'. The function drops the trailing digits.
DateTimeParse('Oct 4, 22 11:59:57.99', '%b %d, %y %H:%M:%S.%3') returns '2022-10-04 11:59:57.990'. The function adds zeros to match the %3 format.
DateTimeParse('Oct 4, 22 11:59:57.99', '%b %d, %y %H:%M:%S,%3') returns [Null] and a warning that ',' instead of '.' was expected.
DateTimeParse('Mar 10, 24 01:00:00 PST', '%b %d, %y %T %Z') returns '2024-03-10 04:00:00’ if the local time zone is Eastern.
DateTimeParse('Mar 12, 24 01:00:00 PST', '%b %d, %y %T %Z') returns ‘2024-03-12 05:00:00’ If the local time zone is Eastern because Eastern time has already changed to EDT (Eastern Daylight Time).
DateTimeParse('Mar 10, 24 01:00:00', '%b %d, %y %T %Z') returns ‘2024-03-10 01:00:00’ if the local time zone is Eastern. Even though the format asked for time zone information, none was present in the data. Without knowing the time zone for the input data, no conversion is done.
DateTimeParse('Mar 10, 24 01:00:00 PST', '%b %d, %y %T') returns ‘2024-03-11 01:00:00’. As in the above example, if the format does not ask to use time zone information, any data that might have time zone data is ignored.
DateTimeQuarter
DateTimeQuarter(dt,[Q1Start]: Returns the numeric value for the quarter of the year in which a date-time (YYYY-MM-DD) value falls. Use the optional numeric parameter to indicate the start month for the first quarter (Q1).
Parameters
dt: Date-time data.
Q1Start: Optional numeric parameter to indicate the start month for Q1 (1–12).
Example
DateTimeQuarter("2023-01-03") returns 1. No error because the second parameter is optional.
DateTimeQuarter("2023-05-03", 1) returns 2.
DateTimeQuarter("2023-05-03", 7) returns 4. The optional parameter indicates that Q1 starts in July.
DateTimeQuarter("2023-05-03 12:04:55", 7) returns 4. The optional parameter indicates that Q1 starts in July, the timestamp is ignored.
DateTimeQuarter("2023-01-03", 13) returns Null. The second parameter must be a numeric value between 1–12.
DateTimeQuarter("2023-01-03", 0) returns Null. The second parameter must be a numeric value between 1–12.
DateTimeQuarter("2023-01-03", -1) returns Null. The second parameter must be a numeric value between 1–12.
DateTimeQuarter("12:00:55") returns Null.
DateTimeSeconds
DateTimeSeconds(dt): Returns the seconds portion of the time in a date-time value, including any sub-second precision if applicable.
Parameters
dt: Date-time data.
Example
DateTimeSeconds('11:59:57.99') returns 57.99.
DateTimeSeconds('11:59:57') returns 57.
DateTimeStart
DateTimeStart(): Returns the date and time when the current workflow started running, using your system time zone.
DateTimeToday
DateTimeToday(): Returns today’s date.
Expected Behavior: DateTimeToday Data Type
Despite its name, DateTimeToday() does not return a time value. Rather it only returns a Date with the current date. You can wrap the DateTimeToday() function in the ToDateTime() function to return a date-time value with the time set to midnight of the current day:
ToDateTime(DateTimeToday())
DateTimeToLocal
DateTimeToLocal(dt,[tz]): Converts a UTC date-time to the optional tz argument, else to the local system time zone. The DateTimeToLocal function now supports date-time precision in the dt field.
Note
The result doesn't have any indication about what its time zone is. You need to use DateTimeFormat or modify the result as a string to indicate the time zone if desired. If you use DateTimeFormat, the tz argument should be a name for the same tz used in the conversion. It could use a different style like EST, or Eastern Daylight Time, or America/New_York, or ‘-0400’. For important time zone considerations, go to Time Zone and Daylight/Standard Time Changes.
Parameters
dt: Date-time data.
tz: This is an optional time zone parameter to convert the date-time data to. If (tz) is not provided in the function call, the system time zone is used.
Example
DateTimeToLocal('2014-08-01 20:01:25') converts to the local system time zone (Mountain Time) as 2014-08-01 14:01:25.
DateTimeToLocal('2024-08-15 09:00:00.123') converts the UTC time to the system time, which could be America/New_York, returns ‘2024-08-15 05:00:00.123’, or it could be America/Denver, returning ‘2024-08-03:00:00.123’.
DateTimeToLocal('2024-08-15 09:00:00.123', 'EST') converts the UTC time to Eastern Daylight Time, even though the time specified is Standard Time. The daylight/standard specification is ignored when looking up the time zone for the conversion, so this returns ‘2024-08-15 05:00:00.123’.
DateTimeToUTC
DateTimeToUTC(dt,[tz]): Converts a date-time (in the local system time zone or the provided time zone) to UTC. The input date-time is assumed to be in the given time zone, or else in the time zone of the system on which the workflow is running. The DateTimeToUTC function now supports date-time precision in the dt field.
Note
The result doesn't have any indication that it is in UTC. You need to use DateTimeFormat or modify the result as a string to indicate the time zone if desired. If you use DateTimeFormat, the tz argument should be a name for the same time zone used in the conversion. It could use a different style like UTC, GMT, or Z, or ‘+0000’. For important time zone considerations, go to Time Zone and Daylight/Standard Time Changes.
Parameters
dt: Date-time data.
tz: This is an optional time zone parameter. If the tz is not given in the function call, the system time zone is used.
Example
DateTimeToUTC(DateTimeNow()) returns the Coordinated Universal Time at workflow runtime: 2014-08-01 20:01:25 (where local Mountain time was 2014-08-01 14:01:25).
DateTimeToUTC('2024-08-15 09:00:00.123') converts the time to UTC from the system time, which can be America/New_York, returning ‘2024-08-15 13:00:00.123’, or America/Denver, returning ‘2024-08-15:00:00.123’.
DateTimeTrim
DateTimeTrim(dt,t): Removes unwanted portions of a date-time and returns the modified date-time.
Parameters
dt: Date-time data.
t: Trim type. Options include...
firstofmonth: Trim to the beginning of the month (this does the same as month).lastofmonth: Extend to one second before the end of the last day of the month.year: Trim to midnight on January 1st.month: Trim to midnight on the first day of the month.day: Trim to the day (in other words, midnight). This converts a date-time to a day with a time of zero (not a date).hour: Trim to the hour.minute: Trim to the minute.seconds: Trim to the second and second fractions quotes using keywords from the Keywords for Date-Time Units section.
Trimming a date-time does not round the returned value. For example, the time 15:59:59 trimmed to the hour becomes 15:00:00, not 16:00:00.
Example
DateTimeTrim("2016-12-07 16:03:00","year") returns 2016-01-01 00:00:00.
DateTimeTrim('2016-12-07 11:59:57.99','msec') returns 2016-12-07 11:59:57.990.
DateTimeTrim('2016-12-07 11:59:57.99',4) returns 2016-12-07 11:59:57.9900.
DateTimeTrim('2016-12-07 11:59:57.123456789','milliseconds') returns 11:59:57.123.
DateTimeYear
DateTimeYear(dt): Returns the numeric value for the year in a date-time value.
Parameters
dt: Date-time data.
Example
DateTimeYear("2017-03-24 11:43:23") returns 2017.
ToDate
ToDate(x): Converts a string, number, or date-time to a date.
An incoming string should be formatted as YYYY-MM-DD. For example, 2020-10-31.
An incoming number should be formatted as an Excel date format where the number represents the number of days since 01-01-1900. For example, 7000 which corresponds to 03-01-1919.
An incoming date-time should be formatted as YYYY-MM-DD hh:mm:ss. For example, 2020-10-31 12:00:00.
Example
ToDate('2020-10-31') returns 2020-10-31 as a date.
ToDate(7000) returns 1919-03-01 as a date.
ToDate('2020-10-31 12:00:00') returns 2020-10-31 as a date.
ToDateTime
ToDateTime(x): Converts a string, number, or date to a date-time. The ToDateTime function doesn't support precision higher than seconds. Limit Excel values to seconds representation (5 digits, up to 99999).
An incoming string should be formatted as YYYY-MM-DD hh:mm:ss. For example, 2020-10-31 12:00:00.
An incoming number should be formatted as an Excel date-time format where the number represents the number of days since 01-01-1900. For example, 7000.354167 which corresponds to 03-01-1919 at 8:30 AM.
Example
ToDateTime('2020-10-31') returns 2020-10-31 00:00:00 as a date-time.
ToDateTime(7000.354167) returns 1919-03-01 08:30:00 as a date-time.
ToDateTime('2020-10-31 12:00:00') returns 2020-10-31 12:00:00 as a date-time.
Date-Time Function Reference
Refer to these specifiers, separators, keywords, and language parameters for use in date-time functions.
Specifiers
Specifiers always begin with a percent sign (%), followed by a case-sensitive letter or number. The data must include at least a two-digit year.
Tip
If you prefer to view an alphabetized list, you can do so below.
Specifier | Output from DateTimeFormat | Supported Input with DateTimeParse |
|---|---|---|
Year | ||
| Last two digits of the year ("16"). | Up to 4 digits are read, stopping at a separator or the end of the string, and mapped to a range of the current year minus 66 to the current year plus 33. For example, in 2016, that's 1950 to 2049. 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...
|
| All four digits of the year ("2016"). | Two or four digits are read. Two digits are mapped to a range from the current year minus 66 to the current year plus 33. For example, in 2024, that's 1958 to 2057. |
Month | ||
| Abbreviated Month Name ("Sep") | Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not the name of a month (in the specified language). |
| Full Month Name ("September") | Month name or any valid abbreviation of a month name ("Sep", "SEPT."). It returns an error only if the given text is not the name of a month (in the specified language). |
| Abbreviated Month Name. Same as %b ("Sep"). | Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not the name of a month. |
| Month number, 01 to 12. | 1 or 2-digit month number, 1 or 01 to 12. |
Week | ||
| This returns the week number, as 00 – 53, with the beginning of weeks as Sunday. | Not Supported |
| This returns the week number, as 00 – 53, with the beginning of weeks as Monday. | Not Supported |
Day of the Year/Month | ||
| Day of the Month ("01") | 1 or 2 digits, ignoring spaces ("1" or "01"). |
| Day of the month, leading 0 replaced by a space (" 1"). | 1 or 2 digits, ignoring spaces ("1" or "01"). |
| The day of the year, from 001 to 365 (or 366 in leap years) | 3-digit day of the year, from 001 to 365 (or 366 in leap years). |
Day of the Week | ||
| Abbreviated Weekday Name ("Mon") | Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. |
| Full Weekday Name ("Monday") | Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. |
| Day of week as a decimal, 1 to 7, with Monday as 1. | Not Supported |
| Day of week as a number, 0 to 6, with Sunday as 0. | Not Supported |
Hour, Minute, Second | ||
| Hour in 24-hour clock, 00 to 23. | Up to 2 digits for hour, 0 to 23. Not compatible with %p or %P. |
| Hour in 12-hour clock, 01 to 12. | Up to 2 digits for hour, 1 to 12. Must follow with %p or %P. |
| 24 hours, leading zero is space, " 0" to "23". | Up to 2 digits for hour. |
| Minutes, 00 to 59 | Up to 2 digits for minutes. |
| Seconds, 00 to 59 | Up to 2 digits for seconds. |
Miscellaneous | ||
| The number of digits of precision for sub-seconds. | Up to 18 digits of precision for sub-seconds. |
| The date and time for the computer’s locale. | Not Supported |
| The Century Number ("20") | Not Supported |
| Equivalent to %m/%d/%y | Not Supported |
| Same as %b ("Sep") | Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not the name of a month. |
| 12 hours, leading zero is space, " 1" to "12". | Not Supported |
| "AM" or "PM" | Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format). |
| "am" or "pm" | Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format). |
| Time in 24-hour notation. Equivalent to %H:%M:%S | Time in 24-hour notation. Equivalent to %H:%M:%S |
| The date for the computer’s locale. | Not Supported |
| The 12-hour clock time, including AM or PM ("11:51:02 AM"). | Hours:Minutes:Seconds [AM / PM] |
| Offset from UTC time ("–600"). | Supported for input. |
| Full timezone name ("Mountain Daylight Time"). | Supported for input. |
Specifier | Output from DateTimeFormat | Supported Input with DateTimeParse |
|---|---|---|
| The number of digits of precision for sub-seconds. | Up to 18 digits of precision for sub-seconds. |
| Abbreviated Weekday Name ("Mon") | Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. |
| Full Weekday Name ("Monday") | Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date. |
| Abbreviated Month Name ("Sep") | Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not the name of a month (in the specified language). |
| Full Month Name ("September") | Month name or any valid abbreviation of a month name ("Sep", "SEPT."). It returns an error only if the given text is not the name of a month (in the specified language). |
| The date and time for the computer’s locale. | Not Supported |
| The Century Number ("20") | Not Supported |
| Day of the Month ("01") | 1 or 2 digits, ignoring spaces ("1" or "01"). |
| Equivalent to %m/%d/%y | Not Supported |
| Day of the month, leading 0 replaced by a space (" 1"). | 1 or 2 digits, ignoring spaces ("1" or "01"). |
| Abbreviated Month Name. Same as %b ("Sep"). | Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not a name of a month. |
| Hour in 24-hour clock, 00 to 23. | Up to 2 digits for hour, 0 to 23. Not compatible with %p or %P. |
| Hour in 12-hour clock, 01 to 12. | Up to 2 digits for hour, 1 to 12. Must follow with %p or %P. |
| The day of the year, from 001 to 365 (or 366 in leap years). | 3-digit day of the year, from 001 to 365 (or 366 in leap years). |
| 24 hours, leading zero is space, " 0" to "23". | Up to 2 digits for hour. |
| 12 hours, leading zero is space, " 1" to "12". | Not Supported |
| Minutes, 00 to 59 | Up to 2 digits for minutes. |
| Month number, 01 to 12. | 1 or 2-digit month number, 1 or 01 to 12. |
| "AM" or "PM" | Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format). |
| "am" or "pm" | Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format). |
| Seconds, 00 to 59 | Up to 2 digits for seconds. |
| Time in 24-hour notation. Equivalent to %H:%M:%S | Time in 24-hour notation. Equivalent to %H:%M:%S |
| Day of week as a decimal, 1 to 7, with Monday as 1. | Not Supported |
| This returns the week number, as 00–53, with the beginning of weeks as Sunday. | Not Supported |
| Day of week as a number, 0 to 6, with Sunday as 0. | Not Supported |
| This returns the week number, as 00–53, with the beginning of weeks as Monday. | Not Supported |
| The date for the computer’s locale. | Not Supported |
| The 12-hour clock time, including AM or PM ("11:51:02 AM"). | Hours:Minutes:Seconds [AM/PM] |
| Last two digits of the year ("16"). | Up to 4 digits are read, stopping at a separator or the end of the string, and mapped to a range of the current year minus 66 to the current year plus 33. For example, in 2016, that's 1950 to 2049. 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...
|
| All four digits of the year ("2016"). | Two or four digits are read. Two digits are mapped to a range from the current year minus 66 to the current year plus 33. For example, in 2024, that's 1958 to 2057. |
| Offset from UTC time ("-600"). | Supported for input. |
| Full timezone name ("Mountain Daylight Time"). | Supported for input. |
Separators
Insert separators between DateTime specifiers to form a format string.
Separator | Output from DateTimeFormat | Supported Input with DateTimeParse* |
|---|---|---|
| / | / or - |
| - | / or - |
space | A Space | Any sequence of white space characters. |
| A Newline | Not Supported |
| A Tab | Not Supported |
other | Other characters, like comma, period, and colon. | Other characters, like comma, period, and colon. |
*DateTimeParse accepts forward slashes (/) and hyphens (-) interchangeably. However, commas, colons, and all other separators must match the incoming data exactly.
Keywords for Date-Time Units
These keywords are compatible with the DateTimeAdd, DateTimeDiff, and DateTimeTrim functions. They are not case-sensitive. The accepted keywords for each unit are comma-separated.
In some cases, we accept abbreviations up to the full keyword. These are specified with upto in the list below and range from the shortest acceptable abbreviation to the full keyword. For example, for seconds we accept sec and seconds, as well as anything in between, like seco. Please note that keywords are in English and translation into other languages is not supported.
You can also use numeric values as keywords for sub-seconds time. This includes units that are not specifically called out in the list below. For example, you can use the keyword "10" to indicate 10 digits of precision.
yea upto yearsmon upto monthsday, dayshou upto hoursmin upto minutessec upto secondsds, dsec, dsecs, deciseconds, 1cs, csec, csecs, centiseconds, 2ms, msec, msecs, milliseconds, 3us, usec, usecs, microseconds, 6ns, nsec, nsecs, nanoseconds, 9ps, psec, psecs, picoseconds, 12fs, fsec, fsecs, femtoseconds, 15as, asec, asecs, attoseconds, 18
For some examples of DateTime field size at various units of precision, refer to this table:
Name | Abbreviation | DateTime Size | Example |
|---|---|---|---|
Seconds |
| 19 (default) | 2023-02-24 01:23:45 |
Deciseconds (1) |
| 21 (19 + 1 + 1) | 2023-02-24 01:23:45.1 |
Centiseconds (2) |
| 22 (19 + 1 + 2) | 2023-02-24 01:23:45.12 |
Milliseconds (3) |
| 23 (19 + 1 + 3) | 2023-02-24 01:23:45.123 |
Microseconds (6) |
| 26 (19 + 1 + 6) | 2023-02-24 01:23:45.123456 |
Nanoseconds (9) |
| 29 (19 + 1 + 9) | 2023-02-24 01:23:45.123456789 |
Picoseconds (12) |
| 32 (19 + 1 + 12) | 2023-02-24 01:23:45.123456789012 |
Femtoseconds (15) |
| 35 (19 + 1 + 15) | 2023-02-24 01:23:45.123456789012345 |
Attoseconds (18) |
| 38 (19 + 1 + 18) | 2023-02-24 01:23:45.123456789012345678 |
Language Parameters
These are the compatible values for the "l" (language) parameter, which is supported by the DateTimeFormat and DateTimeParse functions.
English Language Name | Native Language Name | Language Code |
|---|---|---|
English | English | en |
Italian | Italiano | it |
French | Français | fr |
German | Deutsch | de |
Japanese | 日本語 | ja |
Spanish | Español | es |
Portuguese | Português | pt |
Chinese | 简体中文 | zh |
In addition to the above values, values with at least 2 characters in length that begin with any of the above are also acceptable. For example, eng, engl, engli, etc. for English or esp, espa, sp, spa, span, etc. for Spanish/Español.
Time Zone Naming
There are many styles for naming time zones. Names can be long (Mountain Time), short (MT), specific (MDT, MST), or generic (MT). The Internet Assigned Names Authority (IANA) maintains a database of time zone information. Its style of name is Region/City (for example, America/Los Angeles, Europe/Paris).
Time information can also be marked with its offset relative to Coordinated Universal Time (UTC). For example, 11:31-0500 is a time zone that is 5 hours behind UTC (the same moment in UTC is 16:31). This time could be Central Daylight Time, Eastern Standard Time, or Peru Time. The IANA style is the canonical name for a time zone. Other names might be accepted depending on the user’s locale.
Alteryx uses a widely used library (ICU) to look up time zone information. If your workflow references a time zone that can't be found, the workflow reports an error.
Warning
Please be aware that short names can be ambiguous. For example, IST can be Indian, Israel, or Ireland Standard Time. As such, be careful with your input data.
Alteryx recommends replacing short time zone names with long or IANA-style names to ensure that your data is handled properly. You can use the Find Replace tool to do this.
To find a time zone by name, if the Designer language is set to a language other than English (via User Settings > Language and Region), Alteryx first asks ICU to look up the time zone using that language. If it fails to find the time zone with that language, Alteryx then asks ICU to look up the time zone using English. It happens that in some regions, English abbreviations are used even when the local language has a long name for the zone.
Alteryx also attempts to look up the name in each supported language, starting with the Designer interface language (or the language specified in the function).
Tip
You should be aware of the names used for time zones in your data, and possibly use the Find-Replace tool to ensure that you get appropriate names for time zones.
Time Zone and Daylight/Standard Time Changes
Time zones that have daylight and standard time changes encounter complications when trying to interpret times near the shift from daylight to standard and vice versa.
Shift to Daylight Time
There is an hour around the shift to daylight time (Summer) that is invalid. In the US when local standard time was about to reach March 10, 2024, 2:00:00 AM, clocks were turned forward 1 hour to March 10, 2024, 3:00:00 AM local daylight time instead.
As a result, there was no 2024-03-10 02:30:00 Eastern Time, for example. This time is invalid—there is no such time. However, Alteryx libraries don't detect this problem. If you convert DateTimeToUTC('2024-03-10 02:30:00', 'Eastern Time'), the code applies the offset for standard time (5 hours), and returns '2024-03-10 07:30:00' without any warning of the invalid input.
Shift to Standard Time
There is an hour at the shift to standard time that is ambiguous. In the US, when local daylight time is about to reach November 3, 2024, 2:00:00 AM, clocks are turned backward 1 hour to November 3, 2024, 1:00:00 AM local standard time instead.
2024-11-03 01:30:00 Eastern Time can be either Eastern Standard Time or Eastern Daylight Time. Since it is a very common error to specify EST for a time that is really in Daylight, or EDT for a time that is really Standard, the libraries generally ignore the season specification when looking up time zone information.
As a result, Alteryx can't convert the ambiguous hour from Eastern Time to UTC by using the specified season correctly. It applies the offset for standard time (5 hours) regardless of whether EST or EDT is specified as the time zone to use, that is...
DateTimeToUTC('2024-11-03 01:30:00', 'Eastern Daylight Time')returns 2024-11-03 06:30:00.DateTimeToUTC('2024-11-03 01:30:00', 'Eastern Standard Time')returns 2024-11-03 06:30:00.DateTimeToUTC('2024-11-03 01:30:00', 'Eastern Time')returns 2024-11-03 06:30:00.
Notes and Limitations
Date Support
Alteryx Designer can't process dates prior to January 1, 1400.
Alteryx supports dates in years from 1400 to 9999. We assume all dates are in the Gregorian Calendar, which is the civil calendar used now in most of the world. All computations (adding and subtracting) ignore leap seconds.