- Welcome to the Knowledge Base
- Supported browsers
- Landing Pages
- HTML Editor
- Welcome Messages & Bulletins
- Exporting Data
- Multi Language
- Notifications & Reminders
- Forecasts & Quotas
- Introduction to Importing
- Preparing your Import Data
- Workbooks Import Wizard
- Managing your Imports
- Introduction to Marketing
- Mailing Lists
- Marketing Campaigns
- Upload Library
Workbooks Web Insights
- Tracking Code
- Setting up the Plugin
- Viewing Web Insights Data on your Form Layouts
- Domain Names and Online Activities
- 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
- Installing the Event Management Portal
- Amending Event Portal Parameters
- GatorMail Integration
- Creditsafe Integration
- People & Organisations
- Introduction to Reporting
- Displaying Reports
- Creating Reports
- Reporting Explained
- Emailing Scheduled Reports
- Sharing Reports
- Using Calculated Columns
- Displaying reports within Record Views
- Audit Reporting
- Compliance Record
- Data Enrichment
- Introduction to Transaction Documents
- Credit Notes
- Customer Orders
- Supplier Orders
- Contract Management
- Adobe Sign 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
- Outlook Connector
- Exchange Server Sync
- Workbooks Mobile Client
- 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
- Record Templates
- Form Layouts
- Customising relationships between parties
- Opportunity Stages
- PDF Configuration
- Releases & Roadmap
- Workbooks Glossary
- Contact Support
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 depend on how your Accounting Periods are set up to run from, but will return a value between 1 and 4.
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)