âShow Table of Contents
You can use the following functions in the expression editor to create, manipulate, and format date and time strings. The functions take dates or strings as input, and then return strings as output. The native format of the functions is:
For example: 2016-02-10 14:55:00
If a date/time string is in this format, Alteryx can use it directly without parsing â you can use the Select tool to change to an appropriate data type. If a date/time string is not in this format, use the Date/Time tool or DateTimeParse function to convert the string to a format Alteryx can use.
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.
DateTimeFormat([DateTime_Out],"%d-%m-%Y") will return 22-04-2008 for the date April 22, 2008 (ISO format: 2008-04-22).
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.
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 will go to the last day of that month.
To find yesterdayâs date:
DateTimeAdd (DateTimeToday(), -1, âdaysâ)
To get the first of next month:
DateTimeAdd(DateTimeFirstOfMonth(), 1, âmonthsâ)
Add one month to the date 2016-01-30:
DateTimeAdd(â2016-01-30â, 1, âmonthâ)
Result: 2016-02-29 (because February does not have a 30th)
Subtract one month from the date 2016-03-30:
DateTimeAdd(â2016-03-30â, -1, âmonthâ)
Result: 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th)
For Month and Year differences, a month is only counted when the end day reaches the start day, ignoring the time of day.
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.
Result: 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â)
Result: 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â)
Result: 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â)
Result: 1 (because the start and end are now the same day of the month)
DateTimeDiff(â2017-02-28â, â2016-02-29â, âMonthsâ)
Result: 11 (even though the 28th is the last day of February in 2017, the 28 is less than 29)
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.
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)
Often parts of the date/time are not needed, and keeping them can lead to complications.
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.
Date/Time specifiers are used by the DateTimeParse and DateTimeFormat functions. Alteryx defines the parts of a date/time with these specifiers, 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 are used in both reading and writing dates and always begin with a percent sign (%), followed by a case-sensitive letter.
For example, the common (US) format of 03/13/2006 is specified with the string %m/%d/%Y
The format string used for reading a date should match the data as closely as possible. Alteryx will accept either '/' (forward slash) or '-' (hyphen) separators when reading a date. However, ',' (comma), ':' (colon), and all other separators must match exactly or the input data will be reported as an error.
For example: you can parse the date 03/20/04 with the format string %d/%m/%y, while the date 07-04-2006 needs %d-%m-%Y.
When using Date Time Functions, at least a two digit year must be part of the initial data.
|%a||Abbreviated weekday name ("Mon")||Abbreviated weekday name ("Mon"). Note that Alteryx will not check that the specified day name is valid for a particular date.|
|%A||Full weekday name ("Monday")||Full weekday name ("Monday"). Note that Alteryx will not check that the specified day name is valid for a particular date.|
|%b||Abbreviated month name ("Sep")||Abbreviated month name ("Sep")|
|%B||Full month name ("September")||Full month name ("September")|
|%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")||Two digits, ignoring spaces ("01")
|%D||Equivalent to %m/%d/%y||Not supported|
|%h||Same as %b ("Sep")||Abbreviated month name ("Sep")|
|%H||Hour in 24 hour clock, 00 to 23||Two digits for hour, 00 to 23|
|Hour in 12 hour clock, 01 to 12||Not supported|
|%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"||Two digits for hour|
|12 hours, leading zero is space, " 1" to "12"||Not supported|
|%M||Minutes, 00 to 59||Two digits for minutes|
|%m||Month number, 01 to 12||Two digit month number, 01 to 12|
|%p||"AM" or "PM"||Not supported|
|%P||"am" or "pm"||Not supported|
|%S||Seconds, 00 to 59||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 will return 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 will return 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")||The two digit year|
|%Y||All four digits of the year ("2016")||The four digit year|
|%Z||Full timezone name (âMountain Daylight Timeâ)||Not supported|
|%n||A newline||Any sequence of white space characters|
|%t||A tab||Any sequence of white space characters|
|space||A space||Any sequence of white space characters|
|/||/||/ or -|
|-||-||/ or -|
|other||Other characters, such as comma, period, etc.||Other characters, such as comma, period, etc.|
|%A, %d %B, %Y||Monday, 01 August, 2016|
|%d %B, %Y||01 August, 2016|
|%a, %B %d, %Y||Mon, August 01, 2016|
|%b %d||Aug 01|
|%B %d, %Y||August 01, 2016|
|%B, %Y||August, 2016|