- Welcome to the Knowledge Base
- Supported browsers
- Landing Pages
- HTML Editor
- Welcome Messages & Bulletins
- Exporting Data
- Multi Language
- Notifications & Reminders
- Workbooks Mobile Client
- Forecasts & Quotas
- Introduction to Importing
- Preparing your Import Data
- Workbooks Import Wizard
- Managing your Imports
- Introduction to Marketing
- Marketing Campaigns
- Mailing Lists
- Upload Library
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
- Introduction to Event Management
- Enabling the Event Management Module
- Using Event Records
- The Event Management Portal
- Compliance Records
- Setting up the Integration
- Sending Mailshots using GatorMail
- GatorMail - Dynamic Content
- GatorMail Domain Setup
- GatorMail Field Mappings
- Managing your GatorMail Account
- Setting up GatorMail Workflows
Electronic Signing Tools
- DocuSign Functionality
- Setting up the DocuSign Integration
- DocuSign - Worked Examples
- DocuSign - Reporting
- Adobe Sign Integration
- Docusign Integration
- Creditsafe Integration
- 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 Dropbox
- Workbooks Exchange Server Sync
- Workbooks Outlook Add-in
Workbooks Outlook Connector
- Introduction to the Outlook Connector
- Before downloading the Outlook Connector
- Download/update the Outlook Connector
- Installing the Outlook Connector
- Using the Outlook Connector
- Outlook Connector Troubleshooting
People & Organisations
- Introduction to People & Organisations
- De-duplication and Merge
- Using PCA Predict
- Data Enrichment
- Introduction to Reporting
- Using Reports
- Exporting Reports
- Advanced Reporting
- Introduction to Transaction Documents
- Credit Notes
- Customer Orders
- Supplier Orders
- Contract Management
- Introduction to System Administration
Users & Security
- User Groups
- Account Settings
- Licences & Modules
Email & Integration
- Email Settings
- API Keys
- Web to Case and Web to Lead
- Preference Centre
- MailChimp, Constant Contact & dotMailer
- Postcode Lookup
- SharePoint Integration
- Bank Account Validation
- Creating & Modifying Picklists
- Creating Custom Fields
- Report-based Custom Fields
- Linked Fields
- Record Templates
- Form Layouts
- Customising relationships between parties
- Opportunity Stages
- Custom Records
- PDF Configuration
- Contact Support
- Releases & Roadmap
- 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
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:
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.
Calculate the number of days between a Records’ created at date and today,
Calculate the number of days between the created at date and close date of an Opportunity.
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)
Calculate the number of hours between Now and the last time a record was updated.
TIMESTAMPDIFF(HOUR, updated_at, NOW())
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.
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 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:
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.
Output today’s date in the format ‘MM-YYYY’
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 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:
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.
Add 2 Days to the Created at date
Add 2 Years to the Created at date
DATE_ADD(created_at, INTERVAL 2 YEAR)
DATE_ADD(created_at, INTERVAL 24 MONTH)
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:
And similarly can be used to take off a number of months or years with:
DATE_SUB(date, INTERVAL number unit)
Remove 2 Days from the Created at date
Remove 2 Years from the Created at date
DATE_SUB(created_at, INTERVAL 2 YEAR)
DATE_SUB(created_at, INTERVAL 24 MONTH)