- Welcome to the Knowledge Base
- Introduction
- Training
- Desktop Environment
- Preferences
- Activities
- Cases
- Forecasts & Quotas
- Importing Data
- Leads
-
Marketing
- Introduction to Marketing
- Marketing Campaigns
- Mailing Lists
- Products
- Mailshots
- Upload Library
- Templates
-
Workbooks Web Insights
- Tracking Code
- Setting up the Plugin
- Viewing Web Insights Data on your Form Layouts
- Domain Names and Online Activities
- Gator Popup
- Reporting incorrect Leads created through Web Insights
- Reporting on Web Insights data
- Using UTM Values
- Why aren’t Online Activities being created in the database?
- Why is GatorLeads recording online activities in a foreign language?
- Using HubSpot with Workbooks
- Event Management
- Compliance Records
- GatorMail Integration
- Opportunities
-
Integrations
- Mapping
- Electronic Signing Tools
- Creditsafe Integration
-
Zapier
- Introduction to Zapier
- Available Triggers and Actions
- Linking your Workbooks Account to Zapier
-
Setting up Zaps
- Posted Invoices to Xero Invoices
- Xero payments to Workbooks Tasks
- New Case to Google Drive folder
- New Case to Basecamp Project
- New Workbooks Case to JIRA Ticket
- Jira Issue to new Case
- 123FormBuilder Form Entry to Case
- Eventbrite Attendee to Sales Lead and Task
- Facebook Ad Leads to Sales Leads
- Wufoo Form Entry to Sales Lead
- Posted Credit Note to Task
- Survey Monkey responses to Tasks
- Multistep Zaps
-
Email Integrations
- Email Dropbox
- Workbooks Exchange Server Sync
- Workbooks Outlook Connector
- Scribe/Workbooks Connector
- People & Organisations
- Reporting
- Dashboards
- Transaction Documents
- Auditing
-
Configuration
- Introduction to System Administration
- Users & Security
- Database
- Accounting
- Email & Integration
- Customisation
- Automation
- PDF Configuration
- Contact Support
-
Releases & Roadmap
- Roadmap
- September 2019 Release
- February 2019 - Event Management Release
- January 2019 Release
- October 2018 Release
- May 2018 Release
- February 2018 Release
- January 2018 Release
- November 2017 Release
- September 2017 Release
- June 2017 Release
- March 2017 Release
- December 2016 Release
- August 2016 Release
- January 2016 Release
- Workbooks Glossary
Introduction to Date Functions in Reports
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.
TIP - If your DATEDIFF column is outputting negative numbers, then you will need to change the order that the Dates are in.
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
NOTE - QUARTER will return a value between 1 and 4 depending on the Quarter of the Calendar Year.
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)