You are here: Reference > Date Time Functions

Date/Time Operations for the Alteryx Library Formula Processor

Introduction

The formula processor provides functions for the creating, manipulating, and formatting of dates and times.

The native format of the functions is the extended ISO format of:

yyyy-mm-dd HH:MM:SS (e.g., 2016-02-10 14:55:00)

Strings in this format can be used directly without parsing. The functions take dates or strings as input, and then return strings as output (with one exception).

The functions will report field conversion errors if they are given bad input, or if a parsed date does not match the expected format. Strings that use the correct syntax but denote invalid dates (for example, 2015-02-29 – a nonexistent date) will also produce field conversion errors.

Use the Date/Time tool or DateTimeParse function to convert a string to a supported Date/Time format.

The date/time functions do not handle time zones, as Alteryx uses the time zone of the server. The date/time functions are not sensitive to case.

Current Time Functions

Several functions are based on the current date and time:

Alteryx uses the date and time when the formula is first parsed. In a batch process, this time will be used with each new set of data. This allows for consistency if the process takes a long time.

Input Functions

This function will parse a date in an arbitrary format:

DateTimeParse(<date/time>, <format string>)

Output Functions

The date/time can be put into an arbitrary string format with

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

More formatting options for the date April 22, 2008 are listed below:

Format String

Result

%d-%b-%y

22-Apr-08

%A,%d %B,%Y

Tuesday, 22 April, 2008

%d-%m-%y

22-04-08

%d-%m-%Y

22-04-2008

%d %B, %Y

22 April, 2008

%d/%m/%y

22/04/08

%d/%m/%Y

22/04/2008

%a, %B %d, %Y

Tue, April 22, 2008

%m-%d-%y

04-22-08

%m-%d-%Y

04-22-2008

%m/%d/%y

04/22/08

%m/%d/%Y

04/22/2008

%b %d

Apr 22

%B %d, %Y

April 22, 2008

%B, %Y

April, 2008

%Y-%m-%d

2008-04-22

%Y%m%d

20080422

Conversion Functions

The following functions will convert a DateTime field to either UTC (Coordinated Universal Time) or Local Time. Many web API's allow for date-based queries, and they frequently need the time to be in UTC. To learn more about UTC, see wikipedia.org/wiki/Coordinated_Universal_Time.

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

DateTimeToUTC(DateTimeNow()) will return the Coordinated Universal Time at workflow runtime: 2014-08-01 20:01:25 (where Local Mountain time was 2014-08-01 14:01:25)

Modification Function

Often parts of the date/time are not needed, and keeping them around can lead to complications.  This function gets rid of the excess:

This will return the modified date/time.  The trim types are

• minute:  trim to the minute.

• hour:  trim to the hour

• day: trim to the day (i.e., midnight)

• month: trim to midnight at the first day of the month

• year: trim to midnight on January 1st.

• 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

All except for the last trim the date/time; they do not round.  

For example, the time 15:59:59 trimmed to the hour becomes 15:00:00, not 16:00:00.

Arithmetic Functions

There are also functions that manipulate date/times in various ways.

For example, to find yesterday’s date:

DateTimeAdd (DateTimeToday(), -1, ”days”)

To get the first of next month:

DateTimeAdd(DateTimeFirstOfMonth(), 1, ”months”)

Date/Time Format Flags

Alteryx defines the parts of a date/time with a format specifier, which are gathered together into a format string.  This string is then passed to Alteryx to tell it how to handle the dates in a particular part of the program.

Format specifiers within the format string always begin with the percent sign %, which is followed by a letter, which may be upper or lower case.  

For example, you use %m to specify a month.  

The case of the letter is important,  %M specifies minutes.

These flags are used in both reading and writing dates.  

For example, the common (US) format of 3/13/2006 is specified with the string %m/%d/%Y

Note the slashes between the specifiers. The format string used for reading a date should match the data as closely as possible. The example just given will not read a date that looks like 3-13-06; the format specifies slashes, and if they are not found where expected then Alteryx will report an error.

The year format specifier (both %y and %Y) can read either two or four digit years. Two digit years are always given "20" as the century. Therefore, a two digit year of "99" will translate to "2099", not "1999".

For example: you can parse the date 3/20/04 with the format string %d/%m/%y, while the date 7-4-2006 needs %d-%m-%Y.  

Date/Time Format Specifiers Used in Alteryx

Specifier

Description

Notes

%a

The abbreviated weekday name (Mon, Fri)

 

%A

The long weekday name (Monday, Friday)

 

%b

 

The abbreviated month name (Feb, Oct)

 

%B

The full month name (February, October)

 

%c

The date and time for the computer’s locale.

Output only.

%d

Day of the month as two digits, from 01 to 31

 

%D

 

Equivalent to %m/%d/%y.

Output only.

%H  

 

Hours in a twenty-four hour clock, from 00 to 23.

 

%I

The hour on a twelve hour clock, from 00 to 12.

Output only.

%j

The day of the year from 001 to 365 (or 366 in leap years).

 

%m  

 

The month as a two digit number (01 to 12).

 

%M  

 

Minutes as two digits, from 00 to 59.

 

%p

AM or PM (capital letters).

Output only.  May be modified by computer settings.

%S

 

Seconds, two digits from 01 to 59.

 

%T

 

Time in twenty-four hour notation.  Equivalent to %H:%M:%S.

Output only

%U

This will return the week number, as 00 – 53, with the beginning of weeks as Sunday.

 

%W

This will return the week number, as 00 – 53, with the beginning of weeks as Monday.

 

%X

Adds "AM" or "PM" to the time format.

 

%x

The date, specified by the current locale.

Output only. May be modified by the computer settings.

%y

The two digit year.

 

%Y

The four digit year.

 

%z or %Z

This will give you the time zone, if applicable.

 

 

When using Date Time Functions, at least a two digit year must be part of the initial data.

Related Topics Link IconRelated Topics