Dates are a special form of strings. I've seen lots of questions about dates and there are many helpful articles to be found regarding them. No two authors in Alteryx will solve all problems the same way. In fact, this one author may solve the same problem in multiple ways. Maybe I chose a method before because I didn't realize that there was an easier way to get the job done or because I wanted to use a function that was familiar to the user asking the question. Sometimes I choose to mix things up and to try a different approach. Likely, the reason why I choose one method over another is because I've looked at the data (Profile) and one approach jumped out at me. Ready. Aim. Fire! I do my best to avoid configuring a workflow before I look at the data and imagine the results. What am I doing with my eyes when I look at the data? Once I realize how my mind is working with the dates, I can convert that into Alteryx fairly quickly.
It's a date! Alteryx uses ISO (International Standards Organization) formats for dates. To be stored as a date data type, the structure of the date must be "YYYY-MM-DD". DateTime formats are not in the scope of this article. If all of your data is in this format, you MIGHT be able to store it into a DATE data type. I say that you might be able to do this because I've seen exception data sneak into the mix. 2022-02-29 is NOT a date. You'll get a conversion error with dates that don't exist. We can work with dates and strings that look like dates in Alteryx so don't worry too much about ISO police inspecting your workflows and data. Dirty dates are common, you'll just need to be aware that they can sneak into the process and you'll get your chance to be a date hero.
Use case: I want to extract parts of a date out of a field.
Suppose your'e looking at "1981-10-31" and need to get to the year, the month or the day. How would you do it? Tell yourself the Month. How did you find it? Was it parsed by dashes and you looked for the second part of the field? Did you look at the 6th and 7th position of the string? There are certain functions that will extract date components from a date field (ISO) that are ready to use:
- DateTimeMonth(dt)
- DateTimeDay(dt)
- DateTimeYear(dt)
If you wanted to use alternative string methods to achieve these same results, you could use:
- Substring(dt,5,2)
- Right(dt,2)
- Left(dt,4)
If you wanted to use a date formatting function (datetimeformat help),you could use:
- DateTimeFormat(dt,"%m")
- DateTimeFormat(dt,"%d")
- DateTimeFormat(dt,"%Y")
Use case: I want to compose a date out of a combination of date parts.
Suppose you want to go in the other direction. You don't have a single complete date field and you want to create a valid date. Your incoming data has been parsed into the components of a date. Your Month is July, your Day is the 31st and the Year is 1980. I'll simplify this with 7 as the Month. I don't want to get ahead of myself. How do you create a single date field ("1980-07-31")? Do you put dashes in between the fields after you put the fields into Year, Month, Day order? Watch out! This is a trick question. Please refer to my first sentence, "Dates are a special form of strings." Is 1980 a number or is it a string? You can add "1980" plus "-" plus "7" or "07" plus "-" plus "31" to get to your desired date.
Create a date using strings:
ToDate(ToString([year]) + "-" + ToString([month]) + "-" + ToString([day]))
Create a date using a "PARSE" function:
DateTimeParse(ToString([year])+ToString([month])+ToString([day]),"%Y%m%d")
I like using the parse function, but I power it up by using a SELECT tool before it. I change the data type from a number to a string before the formula is invoked. Now the numbers are strings and my formula becomes:
DateTimeParse([year] + [month] + [day],"%Y%m%d")
I don't need to add the dashes, the Parse function will take care of that for me.
Use case: I want to make use of the current date.
When my job runs, I want to know what time it is:DateTimeNow()
When my job runs, I want to know what time it started: DateTimeStart(). If your job runs over midnight it won't turn into a pumpkin but it will record different dates on different records.
When my job runs, I want to know what is the first day of the month: DateTimeFirstOfMonth()
When my job runs, I want to know what is the last day of the month:DateTimeLastOfMonth()
These functions are all relative to the current date. You can't insert any arguments into the function. That knowledge is coming. Be patient.
Use case: I want tomodify adate.
Suppose you want to add a specific number of time units to a date or you want to compare two dates to find the difference between them. Another modification might be to find the first of the month for that specific date. You might want to find the first of the month for the next month or the last day of the month for that given date. You'll be tempted to try using one of the methods above and finding out that they don't help you. Let's review these modification and comparison functions:
- First Of Month for a given date. How do you solve this with your eyes? Likely you change the day part to the number "1". Here's what that might look like:
DateTimeYear(dt) + "-" + DateTimeMonth(dt) + "-01"
Using the component functions you can piece together the Year and Month (with a dash) and add "-01" to the end of it. If you wanted to use a different function, that's perfectly fine. My feelings are not hurt.
- Last Of Month for a given date. This is a bit more complex. Months can have 28, 29, 30 or 31 days in them. What do you know about the last day of the month? I know that it is always 1 day prior to the 1st day of the next month. I'll first show you how to add time units before I show you this function.
-
I want to add time units to a date field. Suppose you want to add 1 month to a date. First, how do you do this with your eyes? It is pretty tricky. Try taking "2018-08-31" and adding one month to it. What result did you get? Hopefully, you didn't get "2018-09-31" because that would be a dirty date.
DateTimeAdd(dt,1,"month")
You can add "year", "month", "day", "hour", "minute" (or plural forms of these arguments) with either a positive or negative value.
- Last of Month for a given date. Now we're ready to tackle this challenge. We can find the first day of this month, add a month to that value and then subtract one day. That will get us what we need.
DateTimeAdd(DateTimeAdd(DateTimeFormat(dt,"%Y-%m-01"),1,"Month"),-1,"Day")
I've used colors to help you read through this nested function. Given a date field of [dt], I am reformatting the date as the first of the month (green) then using that result to add 1 month (blue) and finally, I am subtracting 1 day from it (magenta).
- Find the difference between two dates. Suppose I want to find out how old someone was on a particular date. If he was born on 1980-07-31 and we wanted to know how old he was on May 2nd, 1998 how would you solve this? For me, I'd look at the year values and do the math. He would soon be 18. I first compute the Years difference and then I check the months. Alteryx will answer you in whole years (or whichever unit you ask).
DateTimeDiff("1998-05-02","1980-07-31","year")
There are macros to help you with your dates. I happen to prefer using the "help" guides. I build formulas for dates because I want to be able to handle any variation that comes my way. One day, someone will find the bottle, rub it and the genie will grant them their wish for easy date functions. I've had my failures and have learned from them. If you've got any date tricks up your sleeve, please do leave a comment here. If you've figured out what happened when my "someone" was 17 or who my "someone" was/is, please share that response here but do use a SPOILER tag.
