Skip to main content

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.

Date Support

Designer can't process dates prior to January 1, 1400.

Alteryx uses the ISO 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.5. 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.

Some DateTime functions require you to set the format for the date. Format strings are comprised of specifiers and separators.

Keywords for Date-Time Units

These keywords are compatible with the DateTimeAdd, DateTimeDiff, and DateTimeTrim functions. Keywords 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 years

  • mon upto months

  • day, days

  • hou upto hours

  • min upto minutes

  • sec upto seconds

  • ds, dsec, dsecs, deciseconds, 1

  • cs, csec, csecs, centiseconds, 2

  • ms, msec, msecs, milliseconds, 3

  • us, usec, usecs, microseconds, 6

  • ns, nsec, nsecs, nanoseconds, 9

  • ps, psec, psecs, picoseconds, 12

  • fs, fsec, fsecs, femtoseconds, 15

  • as, 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

sec

19 (default)

2023-02-24 01:23:45

Deciseconds (1)

ds, dsec, dsecs

21 (19 + 1 + 1)

2023-02-24 01:23:45.1

Centiseconds (2)

cs, csec, csecs

22 (19 + 1 + 2)

2023-02-24 01:23:45.12

Milliseconds (3)

ms, msec, msecs

23 (19 + 1 + 3)

2023-02-24 01:23:45.123

Microseconds (6)

us, usec, usecs

26 (19 + 1 + 6)

2023-02-24 01:23:45.123456

Nanoseconds (9)

ns, nsec, nsecs

29 (19 + 1 + 9)

2023-02-24 01:23:45.123456789

Picoseconds (12)

ps, psec, psecs

32 (19 + 1 + 12)

2023-02-24 01:23:45.123456789012

Femtoseconds (15)

fs, fsec, fsecs

35 (19 + 1 + 15)

2023-02-24 01:23:45.123456789012345

Attoseconds (18)

as, asec, asecs

38 (19 + 1 + 18)

2023-02-24 01:23:45.123456789012345678

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.

Specifier

Output from DateTimeFormat

Supported Input with DateTimeParse

%1 - %18 (a number in the range)

The number of digits of precision for sub-seconds.

Up to 18 digits of precision for sub-seconds.

%a

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.

%A

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.

%b

Abbreviated Month Name ("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.

%B

Full Month Name ("September")

Month name or any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text 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")

1 or 2 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").

1 or 2 digits, ignoring spaces ("1" or "01").

%h

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.

%H

Hour in 24-hour clock, 00 to 23.

Up to 2 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 2 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 2 digits for hour.

%l (lowercase "ell")

12 hours, leading zero is space, " 1" to "12".

Not Supported

%M

Minutes, 00 to 59

Up to 2 digits for minutes.

%m

Month number, 01 to 12.

1 or 2-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 2 digits for seconds.

%T

Time in 24-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 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...

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

%Y

All four digits of the year ("2016").

2 or 4 digits are read. 2 digits are 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.

%z

Offset from UTC time ("-600").

Not Supported

%Z

Full timezone name ("Mountain Daylight Time").

Not Supported

Separators

Separators are inserted 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.

%n

A Newline

Not Supported

%t

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.

Language Parameters

These are the compatible values for the "l" (language) parameter that is supported with 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.

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

DateTimeDay

DateTimeDay(dt): Returns the numeric value for the day of the month in a date-time value.

Parameters

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

Example

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

DateTimeDiff

DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.

Parameters

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

u: Date-time unit, specified between quotes using keywords from the Keywords for Date-Time Units section.

Example

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.

DateTimeFirstOfMonth

DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.

DateTimeFormat

DateTimeFormat(dt,f,l): Converts date-time data from ISO format to another specified format (f), in a specified language (l), for use by another application. Output to String data type. If the specified format carries less precision, the output is truncated.

Parameters

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.

l: Optional language parameter. The language parameter defaults to your selected Designer language. For example, if Designer is set to French, the function reads DateTimeParse(dt,f,"Français") by default. See Language Parameters for accepted language values.

Example

DateTimeFormat([DateTime_Out],"%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).

DateTimeHour

DateTimeHour(dt): Returns the hour portion of the time in a date-time value.

Parameters

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

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

DateTimeMinutes

DateTimeMinutes(dt): Returns the minutes portion of the time in a date-time value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

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 expressed as a selected column or a specified date-time value between quotes.

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(): Returns the current system date and time to seconds.

DateTimeNowPrecise

DateTimeNowPrecise(): Returns the current system date and time with fractions of a second (up to 18 digits of precision). 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.

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): Converts a date string (string) with the specified format (f), in a specified language (l), to the standard ISO 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 expressed as a selected field 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 incoming date string data that you are converting, expressed in a format string between quotes.

l: (Optional) The language of the incoming date string data that you are converting. The language parameter defaults to your selected Designer language. 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.

Example

DateTimeParse("2016/28-03","%Y/%d-%m") returns 2016-03-28.

DateTimeParse([DateTimeNow],"%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 '2022-10-04 11:59:57.000' and a warning that ',' instead of '.' was expected.

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 expressed as a selected column or a specified date-time value between quotes.

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-01-03", ) returns an error. A second parameter is expected.

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 expressed as a selected column or a specified date-time value between quotes.

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.

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): Converts a UTC date-time to the local system time zone. The DateTimeToLocal function currently doesn't support date-time precision in the dt field.

Parameters

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

Example

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

DateTimeToUTC

DateTimeToUTC(dt): Converts a date-time (in the local system time zone) to UTC. The DateTimeToUTC function currently doesn't support date-time precision in the dt field.

Parameters

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

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

DateTimeTrim

DateTimeTrim(dt,t): Removes unwanted portions of a date-time and returns the modified date-time.

Parameters

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 on the first day of the month.

  • day: Trim to the day (i.e., 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 seconds 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 11:59:57.990.

DateTimeTrim('2016-12-07 11:59:57.99',4) returns 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 expressed as a selected column or a specified date-time value between quotes.

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.