Hint: This is part 2 in a series looking at dates and times and how you can work with them in EasyMorph. If you haven't already seen it, I'd highly recommend taking a look at part 1 before continuing.
OK, so hopefully you now understand how dates are often stored in databases and source systems, and more importantly, how they are best stored in EasyMorph so that we can make the most use of them. Let's now look at some of the the common ways to manipulate dates and times.
Constituent parts of dates and times
There are some obvious constituent parts of dates such as the year, the month and the day which it is sometimes useful to separate out. There are also the parts which you may not have considered - but which can also be useful on occasion - such as the day of the year, the quarter or even the ISO week number.
Having date and time fields split out into their parts can be important if you intend to feed the data into a business intelligence tool, as it allows users to quickly select all dates within a year or a month, rather than having to select lots of dates to achieve the same thing.
The "Make date/time columns" action
EasyMorph has the "Make date/time columns" action which can do all of the above and many more. As the name suggests, it also works for times and timestamps as well.
It also couldn't be easier to use; simply select the field containing the dates and/or times and tick the boxes of the constituent parts you'd like EasyMorph to create additional fields for.
EasyMorph will create additional columns for each selected date part. By default they are simply named "Year", "Month", "Quarter", "Day", etc but can be renamed as needed using the "Rename columns" action.
Date functions
As well as simply splitting date or timestamp columns, you may also need to add more advanced logic or calculations into the mix. We'll look at some specific examples later in this series, but for now, it's worth knowing that you can also separate out dates and times using functions in EasyMorph expressions. The expressions editor even has all of the functions grouped for us, making them easy to find.
You will find a function to return all the most common constituent parts of dates that it is possible to obtain using the "Make date/time columns" action. The below table shows the functions and their output based on a timestamp field called "Sales Date", which contains "2025-03-21 12:30:00". You'll also find lots of other date and time functions but we'll look at those a little later.
| Function | Example output | Description |
|---|---|---|
year([Sales Date]) | 2025 | The year as a 4-digit number |
yearquarter([Sales Date]) | 2025-Q1 | The 4-digit year and the quarter (Q1 to Q4) |
yearweek([Sales Date]) | 2025-W12 | The 4-digit year and the ISO week number (W1 to W53) |
yearday([Sales Date]) | 80 | The number of days since the start of the year |
month([Sales Date]) | 3 | The month as a number from 1 to 12 |
isoweeknumber([Sales Date]) | 12 | The ISO week number (W1 to W53) |
day([Sales Date]) | 21 | The day of the month (1 to 31) |
weekday([Sales Date]) | 6 | The day of the week from 1 (Sunday) to 7 (Saturday) |
weekdayname([Sales Date]) | Fri | The abbreviated name of the day of the week |
You can combine these functions together or with other functions to create just about any string containing dates that you can imagine. A few important examples are:
| Function | Example output | Description |
|---|---|---|
monthname(month([Sales Date])) | Mar | The abbreviated name of the month in English |
monthnamelocal(month([Sales Date])) | Mars (French) / März (German) / etc | The abbreviated name of the month based on the current regional locale |
year([Sales Date]) & "-" & yearday([Sales Date]) | 2025-80 | Combine the year and the day of the year. Often used to record manufacturing dates |
right(year([Sales Date]),2) | 25 | The year as a 2-digit number |
There is also the format() function - which we looked at in part 1 of this series - that can also be used to return only part of a date and/or time field. The passed format doesn't need to include all elements of a date or time, thus it can be used to obtain most of the above as well as splitting out time constituent parts also:
| Format | Example output |
|---|---|
| format([Sales Date], "yyyy") | 2025 |
| format([Sales Date], "MMMM") | March |
| format([Sales Date], "ddd") | Fri |
| format([Sales Date], "yyyy-MMM") | 2025-Mar |
Converting timestamps to dates
Another common challenge when working with dates and times is being able to convert timestamps (i.e. a date and time combined such as "2025-03-21 12:30:00") to just a date (i.e. "2025-03-21").
A common approach people use is to reach for the format() function again, using something like format([Order Date],"yyyy-MMM-dd") to return just the date.
There is nothing wrong with doing it this way as long as you keep in mind that the format() function outputs a string and as we learnt in part 1 of this series, dates
stored as strings come with some limitations.
As a quick recap of what we learnt in part 1, dates in EasyMorph are stored as a number of days past an epoch. A day is therefore equal to 1, and times (being a fraction of a day) are decimal values. So if today is 45,737, 6:00 AM today would be 45,737.25 as 6:00 AM is a quarter of the way through a day, mid-day today would be 45,737.5 as mid-day is half-way through a day, etc, etc. Thus, if I wish to convert the timestamp to a date, I need to remove the time part which is the decimal of the number.
As well as the date and time functions mentioned above, we can also use EasyMorph's number functions on dates stored as numbers. And helpfully, the floor() function does exactly what we need. It rounds down numbers to the nearest whole number, effectively removing the decimal part.
| Example | Description |
|---|---|
floor([Order placed]) | Converts any timestamps in the field to dates (e.g. 45,737.25 becomes 45,737) |
floor(now()) | The same as today() |
Calendars and lists of dates
Some times you just need create a list of dates. Maybe the system you wish to extract data from has a limit on how many records it can pass back so looping through a series of dates extracting data for them one at a time will solve the problem. Or maybe your data doesn't have records for all dates but the reporting you wish to produce needs to have all dates included. These are just some of the reasons you might want to create a list of dates.
In EasyMorph, we can generate lists of dates or entire calendar tables using the "Calendar" action. Simply click on "Add data", "Generate data" and then "Calendar".
The action requires that you give it two dates; a start and an end date for the list of dates. These can be manually input (fixed) dates, a date parameter, from the first value in a column, today's date or a number of days back or ahead of today. Keep in mind that the latter will mean the date can change each time the workflow runs. As well as the dates, we can ask EasyMorph to add additional columns breaking down the dates into their component parts without having to add any additional actions.
These additional columns turn a list of dates into a calendar table which can be combined with other data in all the usual ways you are hopefully familiar with in EasyMorph. As touched on above, they can even be useful standalone for pushing into some business intelligence tools to be linked with other data for filtering or data visualization.
Adding and subtracting years/months/days to a date
One of the most common things people want to do with dates is to add or subtract a number of days, weeks, months or even years to calculate another date. For example, if I have an order date and they must be dispatched within 7 days, I may wish to calculate the "Dispatch by" date.
Let's start with adding and subtracting days and you'll start to understand why storing dates as numbers is so useful. When dates ares stored as numbers, today (21st March 2025) is stored as 45,737, yesterday was one less (45,736) and tomorrow will be one more (45,738). And so as you can see, if we wish to add or subtract a number of days, we can do simple maths to calculate the new date. Some common examples would be:
| Example | Description |
|---|---|
today() - 1 | Yesterday's date |
today() - 7 | The date one week ago |
today() + 1 | Tomorrow's date |
today() + 7 | The date in a week's time |
today() + 14 | The date in 2 weeks' time |
You can of course both substitute the today() function for a date or timestamp in your data, and add or subtract as many days as you need.
Things get a little more complex when we wish to add a number of months or years. Your first thought may have been to simply add a larger number of days, but thanks to the Gregorian calendar system used in most of the world, a month can be between 28 and 31 days long, and even a year may be 365 or 366 days long depending upon if it is a leap year. There's no need to panic though, you don't need to write complex logic to work it out, the EasyMorph development team have already done the hard work for you. You'll find two functions already exist that automatically take into account these variations in month and year length - the addmonths() and addyears() functions.
| Example | Description |
|---|---|
addmonths(today(), 1) | One calendar month from today's date |
addmonths(today(), 6) | Six calendar months from today's date |
addyears(today(), 1) | The same date next year |
addyears(today(), 10) | The same date in 10 years' time |
Again, you can substitute the today() function for a field in your data and add as many months and years as you need.
OK, so if I want to subtract months or years instead I just use the subtractMonths() or subtractYears() functions instead, right? Unfortunately, you'll find they don't exist. Instead, we can use the addMonths() and addYears() functions as above but add a negative number of months or years instead:
| Example | Description |
|---|---|
addMonths(today(), -1) | One calendar month before today's date |
addMonths(today(), -6) | Six calendar months before today's date |
addYears(today(), -1) | The same date last year |
addYears(today(), -10) | The same date 10 years ago |
Adding and subtracting hours/minutes/seconds to a timestamp
If you wish to add or subtract values less than a day (i.e. hours, minutes or seconds), then things can get slightly more tricky. Ultimately it is still just a matter of some simple math.
As we saw in part 1 of this series, an hour is 1/24th of a day, a minute is 1/1,440th
and a second is 1/86,400th. As we can see below, we can therefore add or subtract any number of hours minutes or seconds using these values:| Example | Description |
|---|---|
[Order date] + (1/24) | Add one hour |
[Order date] + (1/1440) | Add one minute |
[Order date] + (1/86400) | Add one second |
[Order date] + (1/24) + (30/1440) | Add one hour and 30 mins |
[Order date] - (12/24) | Subtract 12 hours |
Calculating the age or difference between 2 dates
As well as being able to add or subtract time periods, we often also want to calculate the time between two dates or timestamps. I might be starting to sound like a broken record at this stage, but having dates as numbers means yet again, this is just a matter of simple math.
| Example | Description |
|---|---|
[Ship date] - [Order date] | Number of days between the two dates |
today() - [Order date] | The age of the order in days |
One common mistake that people make is to get the two dates the wrong way round. Doing this will give you the correct number of days but as a negative number instead. This happens because the dates are a number of days past the epoch and so the latter date will be a greater number than the earlier date. As a general rule, you should put the most recent date first and subtract the earlier date from it.
Hint: There are situations where it is impossible to know which of two dates may be earlier or later, such as if comparing the dates of birth for 2 people. Rather than
having to build a more complex expression using if statements to work it out, you can simply wrap the subtraction in the
abs() function which returns the always positive absolute value
(e.g. abs([Person1DoB] - [Person2DoB]) will always return a positive number of days).
Both of the above examples provide an answer in days. This is probably exactly what you want when dealing with smaller periods of time, but what if I want to calculate the difference across a greater time period. One often occurring example would be to calculate someone's age from their date of birth. I could just calculate their age in days, then divide this by 365 and round it down to get the number of years like this:
floor((today() - [DoB])/365)
At first glance this might well give you an answer that looks correct, but as mentioned above, years aren't always 365 days long. Throw leap years into the mix and we could well be making someone older than they actually are. Admittedly, the error is likely to be only within a few days, but there is a helpful function that will calculate us an accurate answer.
age([DoB], today())
The age() function accepts 2 dates which can be both from the data or, as is shown above, you can use today's date or even parameters. It gives you the age in whole years, taking into account leap years automatically and even doesn't care which way round you provide the dates.
What's coming in part 3?
Now we know how dates and times work in EasyMorph and have explored how we make make more use of them, splitting them into their constituent parts and using them to perform some basic calculations. In rest of this series we'll look at some of the more advanced topics, such as:
- Finding the start and end of a week or month using a date.
- Dealing with different time zones.
- Converting dates to a different epoch and/or interval.
- Checking if a date is a weekday, weekend, a specific day of the week, etc.
Part 3 in this series will be coming soon!
