Skip to content

Working With Dates and Times

Introduction

The Core scripting language provides several useful functions for working with dates:

Date Functions Relative to Today

The following functions all return a Date object:

Function Date Returned Notes
TODAY Today.
STARTOFWEEK First day of current week. Week starts on a Monday.
ENDOFWEEK Last day of current week. Week ends on a Sunday.
STARTOFMONTH First day of current month.
ENDOFMONTH Last day of current month.
STARTOFQUARTER First day of current quarter.
ENDOFQUARTER Last day of current quarter.
STARTOFYEAR First day of current year. Year starts on January 1st.
ENDOFYEAR Last day of current year. Year ends on December 31st.

Date Time Functions Relative to Now

The following function returns a DateTime object:

Function Date Time Returned Notes
NOW Current Date and Time.

Time Functions Relative to Now

The following function returns the current time as a string:

Function Date Time Returned Notes
TIME Current Time. Formatted as e.g. 15:09.

Converting a String to a Date

You can convert a string to a date if you want to perform date calculations on it:

DATE("2023-02-14")

Date Plus/Minus an Interval

You can take a given date and add or subtract an interval. The allowed units are DAY, MONTH or YEAR.

DATE_ADD(TODAY, 7, DAY) = Today plus 7 days  
DATE_ADD(TODAY, -7, DAY) = Today minus 7 days

DATE_ADD(STARTOFMONTH, 1, MONTH) = First day of the following month  
DATE_ADD(ENDOFMONTH, 3, MONTH) = Three months from the end of the current month

DATE_ADD(STARTOFYEAR, 1, YEAR) = First day of the following year  
DATE_ADD(ENDOFYEAR, 1, YEAR) = One year from the end of the current year

Working with Week Intervals

Since all weeks have seven days there is not a separate unit for week, but you can do the following:

DATE_ADD(STARTOFWEEK, 7, DAY) = First day of next week  
DATE_ADD(ENDOFWEEK, 7, DAY) = Last day of next week

If you want your week to start on a different day, simply add or subtract the relevant number of days:

DATE_ADD(STARTOFWEEK, 5, DAY) = First day of next week (Week starting on Saturday)

Working with Date Fields

In addition to these functions you can also work with dates stored in date fields:

DATE_ADD(["Quote Date"], 21, DAY)  
DATE_ADD(["Invoice Date"], 30, DAY)
DATE_ADD(["Quote Date"], 1, MONTH)
DATE_ADD(["Invoice Date"], 2, MONTH)
DATE_ADD(["Quote Date"], 1, YEAR)
DATE_ADD(["Invoice Date"], 2, YEAR)

Calculating Date Intervals

You can calculate the interval between two given dates with the same choice of units:

// Gives the number of days between start date and end date
DATE_DIFF(["End Date"], ["Start Date"], DAY)

// Gives the number of complete months between start date and end date
DATE_DIFF(["End Date"], ["Start Date"], MONTH)

// Gives the number of complete years between start date and end date
DATE_DIFF(["End Date"], ["Start Date"], YEAR)

Note: The DATE_DIFF function will always return an integer, i.e. the number of complete days, months or years between the dates specified.

DATE_DIFF(DATE("2023-03-13"), DATE("2023-02-14"), MONTH) => 0,
DATE_DIFF(DATE("2023-03-14"), DATE("2023-02-14"), MONTH) => 1,

If you want to know the difference to a greater precision, you can do something like:

// Gives the difference to the nearest whole month
ROUND(DATE_DIFF(["End Date"], ["Start Date"], DAY) * 12/365, 0) 

// Gives the difference in months to one decimal place
ROUND(DATE_DIFF(["End Date"], ["Start Date"], DAY) * 12/365, 1)

But, this might lead to slightly unexpected results. E.g. It is not exactly clear what an interval 0.3 of a month means, as months have different lengths. There is a similar problem with leap years although it is not quite as bad in that case.

In most cases, you will get a more intuitive result using the DATE_ADD function. For example, suppose we have "Start Date" and "End Date" and we want a business rule relating to the difference between them.

We could do something like:

IF DATE_DIFF(["End Date"], ["Start Date"], DAY) * 12/365 > 1.1 
THEN "Outcome A" ELSE "Outcome B" END

But it is not really clear what 1.1 months means.

So instead, we could do:

IF DATE_ADD( DATE_ADD(["Start Date"], 1, MONTH), 3, DAY ) > ["End Date"] 
THEN "Outcome A" ELSE "Outcome B" END

In the second case, it is unambiguous that the rule applies when the end date is more than one month and three days after the start date.