Date formatting in Alteryx seems to be a hard nut to crack for a lot of users. Most of us might get overwhelmed by all the datetime functions available in Alteryx and not take the time to fully understand them. Then while building workflows we run into datetime issues and either find relief on the Community pages or by trying out multiple functions from the Help pages.
In an effort to help those drowning in datetime waters, some users already gave us some great insights. For example,@HenrietteH wrote the Tool Mastery | DateTime) article, and@RodL wrote a Knowledge Base post about the central role of the DateTimeParse and DateTimeFormat functions in Alteryx (Date-Conversions). Although these insights are great, in my opinion, the thing missing is a concise overview of the datetime functions in Alteryx that makes it accessible for all users to understand that it’s really just LEGO (and come on, who doesn’t like LEGO). Therefore I did my best to make such a concise overview of the datetime functions that Alteryx offers and try to get you going in the world of datetime functions.
Where to start?
Let’s keep things simple (for now), you just want to use one datetime function but you don’t have a clue were to start. Well, at least you took the effort to read this blog and probably looked at the visual (hooray!). First, I will tell you something about the visual in general in the part about structuring the datetime functions. Second, and probably more important, I will tell you something about the basics of datetime functions.
Structuring the DateTime functions
Picture a stereotypical teenager’s room - more often than not it’s a mess. They’re told to clean it up once in a while, putting stuff in boxes or drawers (or under the bed). It not only helps to clean up the mess by following a certain structure, it also helps to structure daily routines or structure information (especially when you’re as easily distracted as I am). Structures give us a certain peace of mind.
Go over to the Alteryx Help site to find the DateTime functions, and you will see an extensive overview of all datetime functions, including the parameters (rules or limits that control what something is or how something should be done), specifiers, and separators. It’s really complete and has everything you need to get going (I do love it).
However, you might find it a bit overwhelming at first. Therefore, I took the time to categorize the datetime functions into seven distinct categories that should lead you to the most suitable functions for you to use:
- convert a datetime to a string
- convert a string to a datetime
- convert a string (in iso) or a number to a datetime
- find the difference between two datetime values
- change the datetime to another date or time
- find a number from a datetime
- generate the current (or near) datetime (don’t have one)
The philosophy behind these categories is that you as a user want to achieve a certain goal and that we can distinguish these functions within these seven “goal” categories. When making these categories I structured the information to get a more sensible overview for the most of us.
In total there are 18 functions, and all of them make use of just seven parameters (yes, just seven). For your convenience, I also placed these parameters in the visual (except for all specifiers, separators and language parameters, you can look them up on the Alteryx Help site for DateTime functions). I was surprised by the fact that there are just seven parameters, that doesn’t sound too bad, does it? This means that when you master these seven parameters (or are able to use the visual in conjunction with the Help site for specifiers and separators), you’re basically capable of making all datetime functions.
Understanding the basics
The DateTime tool really is a great tool, as@RodL already pointed out; it essentially gives a convenient representation of the two greatest datetime functions that Alteryx uses, namely DateTimeFormat (convert a datetime to a string) and DateTimeParse (convert a string to a datetime). Understanding these two functions will empower you although they are also arguably the hardest datetime functions to remember which is which! I bet you will probably end up using these two in 95% of the cases. Master these, you master all datetime functions.
What about these functions then? Well, let’s have a closer look. The first function is the DateTimeFormat(dt, f, l) function. This function is used to convert a datetime field into a string. (See Alteryx Help for more information on data types.) The two options to fill in the dt parameter are: 1) write a datetime value in ISO (yyyy-mm-dd HH:MM:SS ) format like “2021-11-04 11:05:34” (you can leave out the time part if you don’t need it) or 2) use an existing datetime or date field. The important thing here to remember is that the Alteryx engine prefers ISO formats. So when do you most likely use this function? This function has the power to spit out string fields with the whole datetime value, or just the year, the day, the number of the day in the month, the name of the weekday and so on. It gives you the power to get varied format representation of the datetime field.
The format that we want is represented in parameter f. To set up this parameter, you need to have some basic understanding about specifiers and separators. I advise you to go to the help site immediately, DateTime functions help!
The first column on the Help site is Specifiers (seen above), a list of the 31 specifiers that Alteryx has, all of them beginning with %. These denote the string format of the output field. You probably already picked up that you have to start that f parameter with %, great! Each specifier represents a component of the datetime field that you used for parameter dt.
For example, %B extracts the “11” from the date [2021-11-04 11:05:34] and returns the full month name (November). %d gives you the day of the month, in this case 04. It will return 00 if you try to enter %H (a time specifier) on the date field. You can make all kind of exotic combinations of these specifiers. For example, DateTimeFormat(“2021-11-04”, “%B%e%A”) returns “November 4Thursday,” isn’t that exotic! Most of the time however, you will probably keep the same formatting (or just switch the months and years) like “%d%m%Y”.
The more experienced users reading this blog will probably laugh at my formatting: “why is this guy not using any separators??” Separators make even more sense of the formatting in parameter f; they give structure to the format. As the name implies, they separate the specifiers. Separators mostly take the form of / , -, or a space, but you can also get interesting by using @ for example. Although you don’t need to use them, you probably will use them in the majority of your cases. To get back to the ISO format, it will end up like “%d-%m-%Y”, notice that you always have the specifiers starting with % followed by a letter and then a separator. You can also have multiple specifiers after each other followed by multiple separators like “%Y--------", I’m not sure who would use that to be honest).
The final parameter is the optional language parameter l, that I personally never use (that might be because Dutch is not supported). Some of the specifiers represent names of days or months, so you want to present those names in your preferred language. Alteryx made a smart trick for that. This parameter will return day or month names in the language of your preference. To do that you have to fill in the corresponding language code - again, look at the help site, it’s all there (Language Parameters)! For example, DateTimeFormat(“2021-11-04”,”%B-%A”, “es”) returns “noviembre-jueves”, the Spanish representation of “November-Thursday”.
Now that you understand the DateTimeFormat function, it’s time to dive into the DateTimeParse function. Thank goodness I don’t have to write as much for this function as it is a lot of repetition of what you just learned. The DateTimeParse function converts a string to a datetime format. Again, you have two options to fill in the dt parameter: 1) write a datetime value in ISO format (yyyy-mm-dd HH:MM:SS or 2021-11-04), remembering that the time part is not necessary, or 2) take an existing string field (for those paying attention, these are almost identical to the DateTimeFormat options). The important thing to remember is that Alteryx wants to spit out an ISO format date or datetime, so you have to clarify to what kind of format you are feeding it (“the machine”).
After filling the dt parameter you must specify the f parameter as with the DateTimeFormat function. As stated, we use the f parameter to clarify what kind of format we are feeding Alteryx. Read the part about specifiers and separators again to build out the f parameter for the resulting string. The only difference from DateTimeFormat is that DateTimeParse can handle 19 specifiers instead of 31 (just read through the list, Datetime functions help).
One last example: if you have a string field with the format of “20-11-2021” then we need to specify the f parameter as “%d-%m-%Y”, built it out using the right specifiers and separators. So it’s about matching f with the incoming string format so that Alteryx can translate it to the ISO format, that’s all!
You just learned the two most important datetime functions in Alteryx. Now you are ready to fix 95% of your datetime problems (that’s my best guess at least). You’ve learned about these important functions and you have some fundamental understanding of the ISO format that Alteryx loves (yyyy-mm-dd HH:MM:SS). Understanding that format is key to understanding most other functions. Let’s have a look at some other functions.
There are 18 datetime functions and you already mastered two of them. Five other functions don’t have any parameters and are more or less self-explanatory, and all are in the category “generate the current (or near) datetime (don’t have one)”). This leaves us with 11 functions, of which all except two use a dt parameter; the two not using the dt parameter are ToDate and ToDateTime. These two functions convert a string, number or date into a date(time), even an Excel number representation of a date(time). This makes it easy for us to understand that basically all 11 functions either want a 1) field as a date(time) data type, or 2) an entered value between quotes (in ISO), like “2021-11-04 11:05:34”, or 3) another function that results in a date(time) in ISO like DateTimeNow() or 4) a Excel number representation of a date(time).
Knowing the options for the dt parameter helps you to construct most of the 11 functions. For example, six functions within the category “find a number from a datetime” all use only the dt parameter. The DateTimeDay("2021-11-04") function returns 4 as it the 4th day in the month. This leaves us with three functions that need some extra attention: DateTimeDiff, DateTimeAdd and DateTimeTrim.
To use the DateTimeDiff(dt1, dt2, u) function (which subtracts the second argument from the first and returns it as an integer), you have to have feed the function two dt values and select a value from the list given for parameter u (units, see my visual). It even works when you compare a date to a datetime, for example: DateTimeDiff("2021-11-07 11:05:34", "2020-11-04", "hours") returns 8843 as the number of hours between the two entries. Parameter u is also used in DateTimeAdd(dt, i, u), so again you have take one of the values from the list. This function lets you add or subtract an amount of time from a date(time) value. Just make sure you always use a whole integer for the i parameter (interval) like 10 or -10 (no fractions). In this case, DateTimeAdd("2021-11-07 11:05:34", -10, "hours") would result in 2021-11-07 01:05:34 and setting i as 10 would return 21:05:34 (24H notation, that is). One last function left to learn! DateTimeTrim(dt, t), which allows you to remove unwanted portions of the datetime field. I would say that parameter t is at least related to parameter u, it is the list of options on how to trim your date (e.g. lastofmonth, year, day, even hours or minutes). The function is here to give you the opportunity to standardize dates. For example DateTimeTrim("2021-11-07 11:05:34", "firstofmonth") returns “2021-11-01 00:00:00.”
You promised us LEGO
At the beginning of my blog, I told you the DateTime functions are just like LEGO. Now that you have some fundamental understanding of the functions, it’s time to give you some examples of how you can stack those functions and make it even more awesome to use them. The real power of these functions comes into play when you stack them. So, could you give us some examples of when you would stack these functions? Of course!
Most of the time I stack these functions whenever I don’t have a date field and want something dynamic (changing every time I run my workflow). For example, let’s say we always want the date(time) 10 days from today. To have today we would just need DateTimeNow() or DateTimeToday() depending whether you need the time. To add some units of time we will need DateTimeAdd(dt, i, u). As we will add to the current date(time), it will result in the following expression DateTimeAdd(DateTimeNow(), 10, “days”), resulting in “2021-11-15 08:56:56” (the date of writing this piece is 2021-11-05).
Another example of using these functions stacked is when you have to write some weird string values in reports stating a day of the week. The requirement is to have a sentence like “Today it’s [name of day of the week]”. In order to make this sentence out of thin air you end up in your Formula tool writing the following expression "Today it's " + DateTimeFormat(DateTimeNow(),"%A"). It’s a good habit to understand that the sentence is a string and you need the day of the week in string format, so basically you want to convert a datetime to a string (first category).
That’s it, you hopefully have become a true master in the order of datetime (make sure to wear that title with pride). I hope this blog and especially the visual helps (new) users to get a better understanding of the DateTime functions in Alteryx.
Links used:
- Tool Mastery DateTime - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-DateTime/ta-p/38302
- Date Conversions - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Date-Conversions/ta-p/3587
- Help Page DateTime functions - https://help.alteryx.com/current/designer/datetime-functions
- Help Page DateTime tool - https://help.alteryx.com/current/designer/datetime-tool
Banner image by ajale-1481387
Astronomical clock image by mcsc1995-3539629
Clock tower image by Metin Ozer
FAQs
What are the functions of DateTime? ›
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.
What is %B in date format? ›For example, %d %B %Y represents dates that appear in the format of 21 February 2018. As another example, %b %e %Y represents dates that appear in the format of Feb 5 2018. For example, you can specify %B for dates that contain strings like February, FEBRUARY, or FEBruary.
What is the most popular DateTime format? ›The ISO 8601 notation is today the commonly recommended format of representing date and time as human-readable strings in new plain-text communication protocols and file formats. Several standards and profiles have been derived from ISO 8601, including RFC 3339 and a W3C note on date and time formats.
What is the format of DateTime example? ›dd/MM/yyyy — Example: 23/06/2013. yyyy/M/d — Example: 2013/6/23. yyyy-MM-dd — Example: 2013-06-23. yyyyMMddTHH:mmzzz — Example: 20130623T13:22-0500.
What are the different types of date functions? ›- DATE - format YYYY-MM-DD.
- DATETIME - format: YYYY-MM-DD HH:MI:SS.
- TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
- YEAR - format YYYY or YY.
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.
What is T and Z in timestamp? ›The T is just a literal to separate the date from the time, and the Z means “zero hour offset” also known as “Zulu time” (UTC).
What is the difference between %b and %b in strptime? ›Additionally, %b signifies abbreviated month name (just the first 3 letters), while %B signifies the full month name. That's probably what you're looking for.
What is the H MM format? ›h:mm tt - 12-hour time format without leading zero for hours that also displays minutes, and AM/PM indicator.
What is the US standard datetime format? ›The United States is one of the few countries that use “mm-dd-yyyy” as their date format–which is very very unique!
What is the most accurate date format? ›
The international standard recommends writing the date as year, then month, then the day: YYYY-MM-DD.
Which datetime format is easiest to parse? ›...
My ultimate recommendation is ISO 8601 (2004) in UTC .
- it's always a constant length.
- it's the format used natively by JSON (eg. ...
- it's the format and profile recommended by the W3C.
The Julian date format is a five digit number broken into two parts: a two-digit representation of the year followed by a three-digit representation of the day of the year. For example, January 1st, 1999 is 99001 in Julian format.
What is Z in datetime format? ›The Z stands for the Zero timezone, as it is offset by 0 from the Coordinated Universal Time (UTC).
What is the default format of datetime now? ›datetime. now() method contains the year, month, day, hour, minute, second, and microsecond (expressed as YYYY-MM-DD hh:mm:ss. ffffff ). It also accepts an optional time_zone parameter, which is set to None by default.
What is the example for date function? ›For example, DATE(108,1,2) returns January 2, 2008 (1900+108). If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2008,1,2) returns January 2, 2008. If year is less than 0 or is 10000 or greater, Excel returns the #NUM!
What are the names of any two date functions? ›| This function | Returns |
|---|---|
| DayOfWeek | A number representing the day of the week on which a date occurs. |
| DayOfYear | The number of days from the beginning of the year of a date. |
| Month | A number from 1 to 12, representing the month of the year in which a date occurs. |
| MonthName | The full name of the month for a date. |
| Format | Date order | Description |
|---|---|---|
| 1 | MM/DD/YY | Month-Day-Year with leading zeros (02/17/2009) |
| 2 | DD/MM/YY | Day-Month-Year with leading zeros (17/02/2009) |
| 3 | YY/MM/DD | Year-Month-Day with leading zeros (2009/02/17) |
| 4 | Month D, Yr | Month name-Day-Year with no leading zeros (February 17, 2009) |
The datetime data types are DATE, TIME, and TIMESTAMP.
How do I get a date from a datetime? ›If we need to get the current date and time, you can use the datetime class of the datetime module. Here, we have used datetime.now() to get the current date and time. Then, we used strftime() to create a string representing date and time in another format.
Is datetime the same as TIMESTAMP? ›
Timestamp is a synonym for rowversion, according to the documentation, and it's created automatically and guaranteed1 to be unique. Datetime isn't one of them; it's merely a data type that handles dates and times and may be customised by the client on insert, for example.
What is the syntax for timestamp? ›Timestamp values are accepted in the format 'yyyy‑MM‑dd HH:mm:ss. SSSSSS' , and can consist of just the date, or just the time, with or without the fractional second portion. For example, you can specify TIMESTAMP values such as '1966‑07‑30' , '08:30:00' , or '1985‑09‑25 17:45:30.005' .
What is Z vs ZZ in date format? ›The count of pattern letters determine the format. Zone: 'Z' outputs offset without a colon, 'ZZ' outputs the offset with a colon, 'ZZZ' or more outputs the zone id. I note that this Joda-Time documentation uses the verb "outputs" rather than "matches".
What is the basic timestamp format? ›The default format of the timestamp contained in the string is yyyy-mm-dd hh:mm:ss.
What is the difference between To_datetime and Strptime? ›to_datetime are the same as datetime. strptime , other than the number of digits %f handles. The second-last line passes, the last line throws ValueError: unconverted data remains: .
How to convert a string into datetime? ›strptime() method to convert a string to a datetime object. The strptime() method takes two arguments: the string to be converted and a format string specifying the input string's format. The format string uses a combination of formatting codes to represent the various components of the date and time.
What is the opposite of strftime? ›To put the difference in simple words, strptime refers to parsing time which is used to read time in specific format. Whereas strftime refers to formatting time, which we use to change the format of time to some new format. So, they are quite opposite of each other.
What does XM mean in time? ›The "xm" refers to after midnight, but still on the same schedule day.
What is the format of UTC time? ›The UTC Time Format
For instance, UTC time in ISO-8601 is xx:xx:xxZ. The Z letter is added without a space. Another UTC format is a 12-hour clock time (AM/PM) xx:xx:xx. Time can be confusing sometimes, but telling time becomes a piece of cake if you understand it you have the right tools.
| Format | Time Segments | Description |
|---|---|---|
| 2 | HH:MM:SS XM | Time is based on a 12 hour system. AM or PM is given. Example: 02:18:23 PM |
| 3 | HH:MM | Time is based on a 24 hour system. Seconds are not given. Example: 14:18 |
| 4 | HH:MM XM | Time is based on a 12 hour system. Seconds are not given. AM or PM is given. Example: 02:18 PM |
Why does the US write dates differently? ›
America inherited the months-first dates from the United Kingdom where it was occasionally used until the early 20th century, according to Reddit. American colonists favoured the monthly format, while the British Empire drifted towards the European style of dd-mm-yyyy.
What is the modern date format? ›| format | British: day-month-year | American: month-day-year |
|---|---|---|
| A | the Fourteenth of March, 2016 | March the Fourteenth, 2016 |
| B | 14th March 2016 | March 14th, 2016 |
| C | 14 March 2016 | March 14, 2016 |
| D | 14/3/2016 | 3/14/2016 |
Select the cells you want to format. Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.
How do I choose a date format? ›Press Ctrl+1 to open the Format Cells dialog. Alternatively, you can right click the selected cells and choose Format Cells… from the context menu. In the Format Cells window, switch to the Number tab, and select Date in the Category list. Under Type, pick a desired date format.
Which is the best datetime library? ›- MomentJS. Does this library need an introduction? ...
- Date-fns. This library is designed to be modular and tree-shakable, allowing you to easily add only the functionality that you need in your application. ...
- Timeago. ...
- MS. ...
- Luxon. ...
- Dayjs. ...
- Fecha.
The widely-recommended solution for storing dates and times is to store the date and time in UTC. This means, whenever you have a user that inserts or updates a datetime value in the database, convert it to UTC and store the UTC value in the database column. Your data will be consistent.
How to extract time from datetime format? ›How to Get the Current Time with the datetime Module. To get the current time in particular, you can use the strftime() method and pass into it the string ”%H:%M:%S” representing hours, minutes, and seconds.
What is Julian date format? ›A Julian date is sometimes used to refer to a date format that is a combination of the current year and the number of days since the beginning of the year. For example, January 1, 2007 is represented as 2007001 and December 31, 2007 is represented as 2007365.
What is the T in the middle of datetime format? ›That is a combined date/time representation as defined by ISO8601. It often has a timezone/offset appended to it, e.g. 2008-09-18T00:00:00Z would denote UTC time.
What is Y vs Y in datetime format? ›They both represent a year but yyyy represents the calendar year while YYYY represents the year of the week.
What are the different datetime formats? ›
| Format | Date |
|---|---|
| MM/dd/yyyy hh:mm:ss tt | 08/05/2006 03:05:15 PM |
| M/d/yy h :m:s tt | 8/5/06 3:5:15 PM |
| ddd MMM dd yyyy | Sat Aug 05 2006 |
| dddd, MMMM dd yyyy | Saturday, August 05 2006 |
To convert a datetime object into a string using the specified format, use datetime. strftime(format). The format codes are standard directives for specifying the format in which you want to represent datetime. The%d-%m-%Y%H:%M:%S codes, for example, convert dates to dd-mm-yyyy hh:mm:ss format.
What are the DateTime functions in SQL? ›SQL Server provides several types of date and time functions such as SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), DAY(), MONTH(), YEAR(), DATEFROMPARTS (), DATETIME2FROMPARTS(), TIMEFROMPARTS (), DATEDIFF(), DATEADD(), ISDATE(), etc. This functions are used to perform operations on date and time input.
What is the function to get DateTime in SQL? ›SQL Server GETDATE() Function
The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format. Tip: Also look at the CURRENT_TIMESTAMP function.
DateTime Properties
It contains properties like Day, Month, Year, Hour, Minute, Second, DayOfWeek and others in a DateTime object. It specifies day of the week like Sunday, Monday etc.
datetime in Python is the combination between dates and times. The attributes of this class are similar to both date and separate classes. These attributes include day, month, year, minute, second, microsecond, hour, and tzinfo.
How to get data from a specific date in SQL? ›- SELECT* FROM.
- table-name where your date-column < '2013-12-13' and your date-column >= '2013-12-12'
The DATETIME data type stores an instant in time expressed as a calendar date and time of day. You select how precisely a DATETIME value is stored; its precision can range from a year to a fraction of a second.
How to select time from datetime in SQL? ›SELECT CONVERT(VARCHAR, getdate(), 108); This will return the time portion in the format hh:mm:ss. For example, if the datetime value is 2023-03-01 11:50:05.627, the result will be 11:50:05. Please note that the number of time formats available in SQL Server is more limited compared to date formats.
How to date from datetime in SQL? ›- Using CONVERT() method.
- Using CAST() method.
- Using combination DATEADD() and DATEDIFF()
- Using TRY_CONVERT() method.
How to set date in datetime in SQL? ›
To update with the current date and time: UPDATE table_name SET date_field = CURRENT_TIMESTAMP; To update with a specific date value: UPDATE table_name SET date_field = 'YYYY-MM-DD HH:MM:SS.
What is the difference between TIMESTAMP and DateTime DateTime? ›The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
How to convert a string to DateTime? ›Converting a String to a datetime object using datetime.strptime() The datetime.strptime() method returns a datetime object that matches the date_string parsed by the format. Both arguments are required and must be strings.
What are the default values of DateTime? ›The default and the lowest value of a DateTime object is January 1, 0001 00:00:00 (midnight). The maximum value can be December 31, 9999 11:59:59 P.M. Use different constructors of the DateTime struct to assign an initial value to a DateTime object.
What is datetime variables? ›DateTime (Type of variable) In french: DateHeure. The DateTime type is used to easily handle a date and a time. The DateTime type can be used to perform calculations (subtraction, addition, ...) on the dates, times and durations. For more details, see Operations that can be performed on the DateTime type.
What is the format of datetime now? ›datetime. now() method contains the year, month, day, hour, minute, second, and microsecond (expressed as YYYY-MM-DD hh:mm:ss. ffffff ). It also accepts an optional time_zone parameter, which is set to None by default.
What is datetime in programming? ›datetime: Used to manipulate the combination of date and time. The attributes are year, month, day, hour, minute, second, microsecond, and tzinfo. 4) datetime. timedelta: Duration between two date, time, or datetime objects with resolution up to microseconds.