◄Show Table of Contents

You are here: Reference > Date Time Functions

Date/Time Functions

Use date/time functions in the expression editor to create, manipulate, and format date and time values.

Alteryx uses the ISO format yyyy-mm-dd HH:MM:SS to represent dates and times. If a date/time value is not in this format, Alteryx reads it as a string. To convert a column for use and manipulation in the date/time format, use the DateTimeParse function in the expression editor or the DateTime Tool.

The date/time functions report conversion errors if they are given bad input, if a date does not match the expected format, or if a date is invalid.

Types of date/time functions

ClosedInput and Output

Convert string data to the native date/time format (yyyy-mm-dd HH:MM:SS) or date/time data to another format.

DateTimeParse(dt,f)

Convert date/time string data in an arbitrary format into a format Alteryx can use (yyyy-mm-dd HH:MM:SS).

ClosedParameters

dt: Date/time string data, expressed as a selected column or a date/time string between quotes. 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 data you are converting, expressed in a format string between quotes.

ClosedExample

DateTimeParse("2016/28-03","%Y/%d-%m") returns 2016-03-28 (when the output data type is String or Date) or 2016-03-28 00:00:00 (if the output data type is DateTime). The format string "%Y/%d-%m" tells Alteryx the format of the incoming date/time data. Alteryx then converts this value to the ISO date/time format.


DateTimeFormat(dt,f)

Convert date/time data (in native/ISO format) into a format for use by another application. Note that you must output to a string data type, not Date or DateTime.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

f: The format to which to convert the data, expressed in a format string.

ClosedExample

DateTimeFormat([DateTime_Out],"%d-%m-%Y") returns 22-04-2008 for the date April 22, 2008 (ISO format: 2008-04-22).


ClosedCurrent Date and Time

Return variants of the current date, time, and month.

DateTimeNow()

Returns the current system date and time.

DateTimeStart()

Returns the date and time when the current workflow started running.

DateTimeToday()

Returns today’s date. The time is set to midnight of the beginning of the day.

DateTimeFirstOfMonth()

Returns the first day of the current month, at midnight.

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).

Alteryx uses the date and time when the formula is first parsed. 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.

ClosedDate and Time Components

Return a specified component of a DateTime value.

DateTimeMonth(dt)

Return the numerical value for the month in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeMonth("2017-03-24 11:43:23") returns 3.

DateTimeMonth("11:43:23") returns [Null], because the incoming data is not valid.

DateTimeDay(dt)

Return the numerical value for the day of the month in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeDay("2017-03-24 11:43:23") returns 24.

DateTimeYear(dt)

Return the numerical value for the year in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeYear("2017-03-24 11:43:23") returns 2017.

DateTimeHour(dt)

Return the hour portion of the time in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

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.

DateTimeMinutes(dt)

Return the minutes portion of the time in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeMinutes("2017-03-24 11:43:23") returns 43.

DateTimeSeconds(dt)

Return the seconds portion of the time in a DateTime value.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeSeconds("2017-03-24 11:43:23") returns 23.

ClosedModification and Comparison

Modify a date/time value with a specified duration, obtain the difference between two date/time values, or remove unwanted portions of a date/time value.

DateTimeAdd(dt,i,u)

Modify a given date/time with a specified duration of time.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

i: Positive or negative integer of time to add or subtract.

u: Date/time unit, specified between quotes: years, months, days, hours, minutes, or seconds.

ClosedExample

DateTimeAdd (DateTimeToday(), -1, ”days”) returns yesterday's date.

DateTimeAdd(DateTimeFirstOfMonth(), 1, ”months”) returns the first of next month.

DateTimeAdd(“2016-01-30”, 1, “month”) returns 2016-02-29 (because February does not have a 30th).

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).


ClosedRead 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 would not have such a day. In that case, it goes to the last day of that month.

DateTimeDiff(dt1,dt2,u)

Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified units.

ClosedParameters

dt1: Date/time data, expressed as a selected column or a specified date/time value between quotes.

dt2: Date/time data to subtract from dt1, expressed as a selected column or a specified date/time value between quotes.

u: Date/time unit, specified between quotes: years, months, days, hours, minutes, or seconds.

ClosedExample

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(“2016-02-15 00:00:00”, “2016-01-15 00:00:01”, “Months”) returns 1 (because the start and end are now the same day of the month).

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).


ClosedRead 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’), and 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.

DateTimeTrim(dt,t)

Remove unwanted portions of a date/time and return the modified date/time.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

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 at the first day of the month
  • day: trim to the day (i.e., midnight). This converts a DateTime to a day with a time of zero (not a date).
  • hour:  trim to the hour
  • minute:  trim to the minute.

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.

ClosedExample

DateTimeTrim("2016-12-07 16:03:00","year") returns 2016-01-01 00:00:00.


ClosedConversion from or to UTC

Convert a DateTime column to either UTC (Coordinated Universal Time) or local time.

DateTimeToLocal(dt)

Converts a UTC DateTime column to the local system timezone.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

DateTimeToLocal('2014-08-01 20:01:25') returns the local system timezone (Mountain Time) as 2014-08-01 14:01:25.


DateTimeToUTC(dt)

Converts a DateTime in the local system timezone to UTC. Many web API's allow for date-based queries, and they frequently need the time to be in UTC.

ClosedParameters

dt: Date/time data, expressed as a selected column or a specified date/time value between quotes.

ClosedExample

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).


Format strings

Date/Time format strings are used by the DateTimeParse and DateTimeFormat functions to tell Alteryx how to parse (read) or format (write) date/time values. Format strings are comprised of specifiers and separators.

ClosedSpecifiers

Specifiers always begin with a percent sign (%), followed by a case-sensitive letter. The data must include at least a two digit year.

Specifier Output from DateTimeFormat Supported Input with DateTimeParse
%a Abbreviated weekday name ("Mon") Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.
%A Full weekday name ("Monday") Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.
%b Abbreviated month name ("Sep") Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%B Full month name ("September") Month name or any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%c The date and time for the computer’s locale Not supported
%C The century number ("20") Not supported
%d Day of the month ("01") One or two digits, ignoring spaces ("1" or "01")
%D Equivalent to %m/%d/%y Not supported
%e Day of the month, leading 0 replaced by a space (" 1") One or two digits, ignoring spaces ("1" or "01")
%h Same as %b ("Sep") Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%H Hour in 24 hour clock, 00 to 23 Up to two digits for hour, 0 to 23. Not compatible with %p or %P.
%I
(capital "eye")		 Hour in 12 hour clock, 01 to 12 Up to two digits for hour, 1 to 12. Must follow with %p or %P.
%j 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)
%k 24 hours, leading zero is space, " 0" to "23" Up to two digits for hour
%l
(lowercase "ell")		 12 hours, leading zero is space, " 1" to "12" Not supported
%M Minutes, 00 to 59 Up to two digits for minutes
%m Month number, 01 to 12 One or two digit month number, 1 or 01 to 12
%p "AM" or "PM" Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format)
%P "am" or "pm" Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format)
%S Seconds, 00 to 59 Up to two digits for seconds
%T Time in twenty-four hour notation. Equivalent to %H:%M:%S Not supported
%u Day of week as a decimal, 1 to 7, with Monday as 1 Not supported
%U This returns the week number, as 00 – 53, with the beginning of weeks as Sunday. Not supported
%w Day of week as a number, 0 to 6, with Sunday as 0 Not supported
%W This returns the week number, as 00 – 53, with the beginning of weeks as Monday. Not supported
%x The date for the computer’s locale Not supported
%X The 12-hour clock time, including AM or PM (“11:51:02 AM”) Hours:Minutes:Seconds [AM / PM]
%y Last two digits of the year ("16") Up to four 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 current year plus 33. (For example, in 2016, that's 1950 to 2049.)
ClosedLimitation with six-digit dates

Because up to four digits are read for the year, formats that are intended to have only two digits without a separator, such as a six-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as four digits. To work around this limitation, you can:

  • Use four 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 two digits in the string.
%Y All four digits of the year ("2016") Two or four digits are read. Two digits are mapped to a range of the current year minus 66 to current year plus 33. (For example, in 2016, that's 1950 to 2049.)
%z Offset from UTC time (“-600”) Not supported
%Z Full timezone name (“Mountain Daylight Time”) Not supported
ClosedSeparators

Separators are inserted between date/time 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
%n A newline Not supported
%t A tab Not supported
other Other characters, such as comma, period, and colon Other characters, such as comma, period, and colon

* DateTimeParse accepts forward slashes ( / ) and hyphens ( - ) interchangeably. However, commas, colons, and all other separators must match the incoming data exactly.

ClosedFormat string examples

Format String Result
%d-%b-%y 01-Aug-16
%A, %d %B, %Y Monday, 01 August, 2016
%d-%m-%y 01-08-16
%d-%m-%Y 01-08-2016
%d %B, %Y 01 August, 2016
%d/%m/%y 01/08/16
%d/%m/%Y 01/08/2016
%a, %B %d, %Y Mon, August 01, 2016
%A, %B%e, %Y Monday, August 1, 2016
%m-%d-%y 08-01-16
%m-%d-%Y 08-01-2016
%m/%d/%y 08/01/16
%m/%d/%Y 08/01/2016
%b %d Aug 01
%B %d, %Y August 01, 2016
%B, %Y August, 2016
%Y-%m-%d 2016-08-01
%Y%m%d 20160801

Learn more about Alteryx at www.alteryx.com | Connect with experts and access Alteryx support options at the Alteryx Community | Send comments on Alteryx Help to helpfeedback@alteryx.com

©2017 Alteryx, Inc., all rights reserved. Allocate®, Alteryx®, Guzzler®, and Solocast® are registered trademarks of Alteryx, Inc.