This page will cover some of the most common Date Functions that you may use when creating Reports. These can then be used to look at progress over time, or the time between updates on an Order.

We will cover a number of different functions as well as examples of how they can be used within Workbooks Reports.

Two key functions that are used in Reporting are CURDATE() and NOW(). Both of these will input the current date or the current date and time, allowing you to calculate the time relative to now.

NOW() - This is used in date calculations to output the current date and time.
CURDATE()  - This is used in date calculations to output the current date.

Date Calculation Functions

DATEDIFF

The DATEDIFF function outputs will calculate the number of days between two dates, these can either be static dates or date Fields in Workbooks.

The Format of a DATEDIFF calculation follows the format of:

DATEDIFF(date1, date2)

In most cases you will want date1 to be the larger date, as DATEDIFF is essentially doing date1 minus date2.

Example 1

Calculate the number of days between a Records’ created at date and today,

DATEDIFF(CURDATE(), created_at)

Example 2

Calculate the number of days between the created at date and close date of an Opportunity.

DATEDIFF(close_date, created_at)

TIMESTAMPDIFF

TIMESTAMPDIFF is similar to DATEDIFF, however it allows you to chose different time periods such as months, weeks or hours so can be used on both Date Fields or Date & Time Fields.

TIMESTAMP follows a similar format to DATEDIFF, but you need to specify the time interval you are using:

TIMESTAMPDIFF(interval, date1, date2)

Example 1

Calculate the number of hours between Now and the last time a record was updated.

TIMESTAMPDIFF(HOUR, updated_at, NOW())

Example 2

Calculate the number of months between the created at and close date of an Opportunity.

TIMESTAMPDIFF(MONTH, created_at, close_date)

 

Date Format Functions

The first functions we will cover are WEEK, MONTH, YEAR and QUARTER. These can be used to show which number week, month, year or quarter a date falls into.

For example:

WEEK('2018-12-31') = 52
MONTH('2018-12-31') = 12
YEAR('2018-12-31') = 2018
QUARTER('2018-12-31') = 4

DATE_FORMAT

DATE_FORMAT takes a date and changes how it appears, this can be used to make dates easier to read.  For example to convert a date to be in the format of MonthName-Year.

The format of DATE_FORMAT follows:

DATE_FORMAT(date, 'format')

The format of a date uses a series of operators that start with a percentage symbol (%). For example, %Y will output a year as YYYY wherease %y will output YY. A full list of these operators can be found at W3 Schools.

Example 1

Output today’s date in the format ‘MM-YYYY’

DATE_FORMAT(CURDATE(), '%m-%Y')

Example 2

Output the created at date of a Record to show ‘YY-MM-DD HH:mm:SS’

DATE_FORMAT(created_at, '%y-%m-%d %k:%i:%s')

We have further examples of how dates can be formatted on our Forum.

DATE_ADD

DATE_ADD does a simple calculation to a date where it adds specified time period to a date and then outputs this new date.

The Format of DATE_ADD follows:

DATE_ADD(date, numberofdays)

However, if you wanted to add on a number of months or years you will need to use the following.

DATE_ADD(date, INTERVAL number unit)

Where unit is a time period such as MONTH or YEAR.

Example 1

Add 2 Days to the Created at date

DATE_ADD(created_at, 2)

Example 2

Add 2 Years to the Created at date

DATE_ADD(created_at, INTERVAL 2 YEAR)

Or

DATE_ADD(created_at, INTERVAL 24 MONTH)

DATE_SUB

DATE_SUB is the reverse function of DATE_ADD, where it subtracts a time interval from the date that has been inputted.

As such it follows the same format:

DATE_SUB(date, numberofdays)

And similarly can be used to take off a number of months or years with:

DATE_SUB(date, INTERVAL number unit)

Example 1

Remove 2 Days from the Created at date

DATE_SUB(created_at, 2)

Example 2

Remove 2 Years from the Created at date

DATE_SUB(created_at, INTERVAL 2 YEAR)

Or

DATE_SUB(created_at, INTERVAL 24 MONTH)