Working with data in Alteryx you're going to encounter dates. A lot. They can be finicky and it's not always clear how to get to where you need to go. I thought I would put everything I've learned so far in one place in order to help someone (very probably me) in the future.
There are a few main things you'll likely be doing with dates: converting them, formatting them, counting them, and iterating over them. I'll cover these in sections.
Converting and Formatting Dates
Going from strings to dates and dates to strings.
Dates come as strings in all shapes and sizes but will need to be converted to a DateTime format in order to be used programmatically in Alteryx. The newest and easiest way to do this is with the DateTime tool under the Parse menu. First you choose which way you are converting, the field you're converting, and a name for the new column (can't be the same as the input). There are also a few different language options available which, while I haven't had a use for yet, work perfectly well. Alteryx helpfully provides a list of common date formats but as likely as not you will need to input your own with the Custom selection.
Even though this is relatively straightforward, you still have to know a little but about how Alteryx represents dates as templates. This is a short string of letters defining how a date should look, not the actual date itself. This can be a little tricky because strings have to match these precisely or else you will output a null when trying to convert string data into dates, including the punctuation.
All the different ways date formats can be represented
Starting date: April 1st, 2015
d = 1
dd = 01 (d and dd are actually interchangable when converting from strings)
dy = Wed
dy. = Wed
day = Wednesday
M = 4
MM = 04 (also interchangable when converting from strings)
Mon = Apr
Mon. = Apr
Month = April
yy = 15
yyyy = 2015
To represent the starting date we would input "Month d, yyyy" and you would have to add the "-st" yourself, which is a whole other blog post. Sometimes you will need to represent time as well:
30 seconds past 4:15pm
hh = 04
HH = 04
mm = 15
ss = 30
I recently had an issue using the DateTime tool with this format: 04:15:30.125. With a little research I learned how to represent the sub-seconds in this example.
ff = 12
fff = 125
However, sometimes the DateTime tool won't make sense for your workflow or you need to convert multiple columns in one step, and you will need to use the DateTime functions in conjunction with the Formula tool or the Multi-Field Formula tool. The simplest but least flexible of these are ToDate() and ToDateTime(). These functions only take string dates in a format that Alteryx already uses for their dates: yyyy-MM-dd (or 2015-04-01). A fun but not necessarily useful thing is you can actually put numbers into these formulas and it will generate dates, in an odd sort of way. Alteryx will convert numbers into days elapsed since December 30th, 1899. It will actually convert anything after a period into time too, so you could represent the date above by entering ToDateTime(42095.6875).
Something to make note of is to always make sure the data type selection under the formula field matches what you are trying to output, or you can spend a not insignificant amount of time wondering why your formulas aren't working as they should.
This brings us to the workhorses of DateTime functions in the Formula tool: DateTimeParse() and DateTimeFormat(), for converting from strings to dates and vice versa, respectively. Essentially an older and more flexible version of the DateTime tool, these functions actually use an entirely separate method of representing date formats that you need to learn in order to use them. I'll list those (the useful ones anyway) for reference here, again using the example date April 1st, 2015 04:15:30.
%d = 1 or 01, interchangeable
%a = Wed
%A = Wednesday
%m = 4 or 04, interchangeable
%b = Apr
%B = April
%h = Apr
%y = 15
%Y = 2015
%a, %A, %b, %B, and %h also work with any abbreviation of the day or month name from 3 characters and up
%H = 4 or 04, interchangeable, 24-hour format
%I = 4 or 04, interchangeable, 12-hour format, must be paired with %p or %P
%p = pm, must follow %I somewhere in the string
%P = PM, must follow %I somewhere in the string
%M = 15, can be one or two digits
%S = 30, can be one or two digits
%1 through %18 = 0, # of digits of precision for subseconds
You have to be a little careful with the time if it is in 12-hour format and here is why. If you have a field with both "4:15:30 am" and "4:15:30 pm" and use the formula DateTimeParse([field name], "%H:%M:%S") they will both convert to "04:15:30".
There are also some special ones you can only use with DateTimeFormat():
%D = 04/01/2015
%T = 16:15:30
%X = 4:15:30 PM
%c = 04/01/2015 4:15:30 PM
%w = 3 (day of the week as a number with Sunday as 0)
A more complicated use case for the above formulas would be to convert 12-hour time to 24-hour time but return it as a string. This could work as follows.
DateTimeFormat(DateTimeParse("7:15 PM", "%I:%M %P"), "%H:%M") would return "19:15".
Finally, there are some functions you can use to return portions of a DateTime to a numeric value. This is less common, but still comes in handy once in a while. These are DateTimeYear(), DateTimeMonth(), DateTimeDay(), DateTimeHour(), DateTimeMinutes(), and DateTimeSeconds().
Common DateTime functions
The general-purpose DateTime functions I'll cover here are DateTimeAdd(), DateTimeDiff(), DateTimeNow(), DateTimeToday(), and DateTimeTrim().
DateTimeAdd() is used to add or subtract time from dates. There are three necessary pieces of information for this function, the original date, the positive or negative difference, and the units to use (anywhere from microseconds to years). The difference has to be an integer so instead of writing "2.5 years" you would need to use "30 months". Positive numbers go forward in time and negative numbers go backwards.
Examples
DateTimeAdd("2015-04-01 04:15:30", 2, "days") = "2015-04-03 04:15:30"
DateTimeAdd("2015-04-01 04:15:30", 1, "month") = "2015-05-01 04:15:30"
DateTimeAdd("2015-04-01 04:15:30", -5, "minutes") = "2015-04-03 04:10:30"
Every once in a while you will need to use this function with a Time variable, however, this function requires a date to work. Here is a workaround - you can pad the time with a fake date. For example, let's say you have a field with the time "05:05:05":
DateTimeAdd("2000-01-01 " + [time], 10, "minutes") = "05:15:05"
DateTimeDiff() is used to figure out the difference between two dates or times. Unlike DateTimeAdd() it can be used with time alone without an issue, no padding necessary. The three arguments it takes are the later date, followed by the earlier date, and then the units of measurement.
Examples
DateTimeDiff("2015-04-01 04:15:30", "2015-06-01 04:15:30", "hours") = 1464
DateTimeDiff("2017-01-01", "2023-01-01", "months") = 72
In a similar quirk to DateTimeAdd() it will only return whole numbers, always rounded down even if the difference is closer to a larger amount of time. These are also output as strings, not integers.
DateTimeDiff("2020-01-01", "2020-02-29", "months") = 1, not 2
An interesting case I've seen is taking the difference between two times and converting into its constituent parts using the MOD function to calculate the remainders.
Days:
DateTimeDiff("2014-07-09 11:07:47", "2014-07-14 08:24:35", "days") = "4"
Hours:
Mod(DateTimeDiff("2014-07-09 11:07:47", "2014-07-14 08:24:35", "hours"), 24) = "21"
Minutes:
Mod(DateTimeDiff("2014-07-09 11:07:47", "2014-07-14 08:24:35", "minutes"), 60) = "16"
Seconds:
Mod(DateTimeDiff("2014-07-09 11:07:47", "2014-07-14 08:24:35", "seconds"), 60) = "48"
[Days] + " days, " + [Hours] + " hours, " + [Minutes] + " minutes, and " + [Seconds] + " seconds" = "4 days, 21 hours, 16 minutes, and 48 seconds"
DateTimeNow() and DateTimeToday() do almost the exact same thing, with DateTimeNow() returning the current time down to the second and DateTimeToday() only going down to the date.
Occasionally you will have a date and actually want less precision, which is where DateTimeTrim() comes in. When you enter a DateTime and a unit it will truncate it to the closest unit you enter, with "00" for time and "01" for dates.
DateTimeTrim("2015-04-01 04:15:30", "hours") = "2015-04-01 04:00:00"
DateTimeTrim("2015-09-23", "years") = "2015-01-01"
Working With Dates
I want to cover a couple more useful processes when working with dates. A common task is generating a row for all the days between two dates in order to make another calculation. Generally, the start and end dates will need to be in the same row already for this to work. Then you can use the Generate Rows tool - in this case creating a new field called "generated time".
Initialization Expression:
[starting time]
Condition Expression:
[generated time] <= [ending time]
Loop Expression:
DateTimeAdd([generated time], 1, "desired unit")
Another task I've encountered is counting business days between two dates, in other words only Monday through Friday. This is a case where DateTimeFormat() comes in handy, in conjunction with the Filter tool. First you will get the day of the week from the date:
Day Number:
DateTimeFormat("2015-04-05", "%w") = "0"
DateTimeFormat("2015-04-06", "%w") = "1"
DateTimeFormat("2015-04-07", "%w") = "2"
DateTimeFormat("2015-04-08", "%w") = "3"
DateTimeFormat("2015-04-09", "%w") = "4"
DateTimeFormat("2015-04-10", "%w") = "5"
DateTimeFormat("2015-04-11", "%w") = "6"
You could also use DateTimeFormat([date], "%a") to get the name of the weekday instead, it's just a little bit more typing. Then you can easily filter out weekend days in the Filter tool.
[Day Number] != "0" AND [Day Number] != "6"
Lastly, a resource I've found really helpful is the Alteryx Weekly Challenge index. Here are some challenges to test your DateTime chops.
#86, #126, #134, #162, #210, #241, #248, #252