Analytics SAQL Reference

SAQL Overview
Enable SAQL Logs in the Browser
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Date Functions

To specify dates in a SAQL query, use date functions and relative date keywords.

Functions

Relative dates are relative to UTC, not local time. Data returned for relative dates reflect dates based on UTC time, which may be offset from your local time.

Note

This table lists SAQL date functions:
Date Function Description
date(year, month, day) Returns a date. Specify 3 dimensions of a date in the following order: year, month, day. For example:
date_diff(datepart,startdate,enddate) Returns an integer representing the interval that has elapsed between two dates.
datepart indicates the interval part to calculate:
  • year
  • month
  • quarter
  • day
  • week
  • hour
  • minute
  • second
startdate indicates the start date.

enddate indicates the end date.

The difference between two dates is calculated based on the difference in the indicated date part. For example, the year difference between two dates is calculated by subtracting the year part of startdate from the year part of enddate.

date_diff("year", toDate("31-12-2015", "dd-MM-yyyy"), toDate("1-1-2016", "dd-MM-yyyy")) would give a result of 1.

Similarly, using the date part month as an example:

date_diff("month", toDate("31-12-2015", "dd-MM-yyyy"), toDate("1-1-2016", "dd-MM-yyyy")) would also give a result of 1.

If startdate is after enddate the result is a negative integer of the difference.
Examples:
Query Result
date_diff(“year", '2004-02-29', '2005-02-28’) 1
date_diff(“year", '2012-1-1', '2012-12-31’) 0
date_diff(“month", '2003-02-01', '2003-05-01’) 3
date_diff(“month", '2004-02-28', '2004-03-31’) 1
date_diff(“quarter", '2012-12-12', '2013-01-05') 1
date_diff(“week", '2012-12-12', '2013-01-05') 3
date_diff(“day", '2012-12-12', '2013-01-05') 24
date_diff(“hour", '2012-12-12', '2013-01-05') 576
date_diff(“minute", '2012-12-12', '2013-01-05') 34560
date_diff(“second", '2016-09-15 19:42:36', '2016-09-16 19:42:36') 86400

Query example:

Invalid examples:
dateRange(startArray_y_m_d, endArray_y_m_d) Returns a fixed date range. The first parameter is an array that specifies the start date in the range. The second parameter is an array that specifies the end of the range. For example:
day_in_month(date) Returns an integer representing the day of the month for a specific date. See day_in_week for usage.
day_in_quarter(date) Returns an integer representing the day of the quarter for a specific date. See day_in_week for usage.
day_in_week(date) Returns an integer representing the day of the week for a specific date. 1 = Sunday, 2 = Monday and so on.

date indicates the reference date.

Example:
day_in_year(date) Returns an integer representing the day of the year for a specific date. See day_in_week for usage.
daysBetween(date1, date2) Returns the number of days between 2 dates as an integer.
The daysBetween() function can’t take dimensions as arguments directly. Pass toDate() and now(​) functions as arguments.
month_days(date) Returns the number of days in the month for a specific date.

date indicates the reference date.

Examples:
Query Result
month_days(toDate('2004-02-12', "yyyy-MM-dd") 29
month_days(toDate('2012-04-07', "yyyy-MM-dd") 30
month_days(toDate('1990-13-11', "yyyy-MM-dd") NULL

Query example:

Invalid examples:
month_last_day(date) Returns the date of the last day of the month for a specific date. See week_last_day for usage.
now() Returns current datetime in UTC. This function is valid in a foreach statement only.

This function is commonly used in daysBetween(​) and toString() functions.
quarter_days(date)s Returns the number of days in the quarter for a specific date. See month_days for usage.
quarter_last_day(date) Returns the date of the last day of the quarter for a specific date. See week_last_day for usage.
toDate(string [,formatString]) Converts a string to a date. If a formatString argument isn’t provided, the function uses the format yyyy-MM-dd HH:mm:ss.

This function is often passed as an argument to daysBetween(​) or toString().

toDate(epoch_seconds) Converts Unix epoch seconds to a date. If epoch_seconds is 0, toDate(epoch_seconds) returns '1970-01-01 00:00:00'.

This function is convenient for adding or subtracting time periods to or from a date. When adjusting dates for time zone differences, adding or subtracting the number of seconds in the time difference produces the correct local date. If the time crosses the local meridian, a different date is produced.

For example, assuming Current_Date is the current date expressed as the number of seconds since '1970-01-01 00:00:00', then the function toDate(Current_Date - 8*3600) subtracts 8 hours. Refer to Working with Time Zones for a practical example.
toString(date, formatString) Converts a date to a string.
This function must take a toDate() or now(​) function as its first argument.
week_last_day(date) Returns the date of the last day of the week for a specific date.

date indicates the reference date.

Examples:
Query Result
week_last_day(toDate('2016-12-08', "yyyy-MM-dd")) 2016-12-10
week_last_day(toDate('2015-07-05', "yyyy-MM-dd")) 2015-07-11
week_last_day(toDate('2012-11-33', "yyyy-MM-dd")) Error

Query example:

Invalid examples:
year_days(date) Returns the number of days in the year for a specific date. See month_days for usage.
year_last_day(date) Returns the date of the last day of the year for a specific date. See week_last_day for usage.

While it’s apparent that this function will always return 31st December, it is included for uses such as finding the number of days to the year end, and for use in a specific locale.

Note

Specify Fixed Date Ranges

To specify a range for fixed dates, use the dateRange() function. Specify the dates in the order: year, month, day.

Example

Specify Relative Date Ranges

To specify a relative date range, use the in operator on an array with relative date keywords. Here are 4 examples:

The relative date keywords are:
  • current day
  • n day(s) ago
  • n day(s) ahead
  • current week
  • n week(s) ago
  • n week(s) ahead
  • current month
  • n month(s) ago
  • n month(s) ahead
  • current quarter
  • n quarter(s) ago
  • n quarter(s) ahead
  • current fiscal_quarter
  • n fiscal_quarter(s) ago
  • n fiscal_quarter(s) ahead
  • current year
  • n year(s) ago
  • n year(s) ahead
  • current fiscal_year
  • n fiscal_year(s) ago
  • n fiscal_year(s) ahead
This table shows the time windows for some of the relative date keywords. In these time window examples, the current day is 2014/12/16 and FiscalMonthOffset 1 (the fiscal year starts on February 1).
Relative Date Keyword Start Date End Date
current day 2014/12/16 00:00:00 2014/12/16 23:59:59
current quarter 2014/10/1 00:00:00 2014/12/31 23:59:59
1 year ago 2013/1/1 00:00:00 2013/12/31 23:59:59
1 month ahead 2015/1/1 00:00:00 2015/1/31 23:59:59
current fiscal_year 2014/2/1 00:00:00 2015/1/31 23:59:59
current fiscal_quarter 2014/11/1 00:00:00 2015/1/31 23:59:59
2 fiscal_quarters ahead 2015/5/1 00:00:00 2015/7/31 23:59:59
current day - 1 year 2013/12/16 00:00:00 2013/12/16 23:59:59
current fiscal_year + 5 days 2014/2/6 00:00:00 2014/2/6 23:59:59

Only standard fiscal periods are supported. See “About Fiscal Years" in Salesforce Help.

Note

Add and Subtract Dates

You can add and subtract dates using the relative date keywords.

Example

Here are examples of time windows for relative date keywords using addition and subtraction. In these time window examples, the current day is 2014/12/16 and FiscalMonthOffset 1 (the fiscal year starts on February 1).

In this query, the start date is 2013-12-16 00:00:00 and the end date is open ended:
In this query, the start date is 2014-12-16 00:00:00 and the end date is 2017-3-31 23:59:59:
Here’s how to determine the end date: the year is 2014, so 2 years ahead is 2016, which has a year end time of 2016-12-31 23:59:59. When you add 3 months, the total end date is 2017-3-31 23:59:59.
In this query, the start date is 2014-2-6 00:00:00 and the end date is 2017-3-31 23:59:59:

Use Open-Ended Relative Date Ranges

To build queries like “List all opportunities closed after 12/23/2014" and “Get a list of marketing campaigns from before 04/2/2015," use open-ended date ranges.

Example

This example shows an open-ended relative date range.

Example

This example shows an open-ended fixed date range. The date format of OrderDate is yyyy-MM-dd.

Working with Time Zones

A practical use of the toDate() function is to calculate time zone changes for an Analytics dashboard. This JSON code fragment uses a computeExpression action in a transformation, which in turn uses a saqlExpression to call the toDate() function. This technique enables a dashboard to show the most appropriate time and date, whether local or UTC.

The example takes an existing date CreatedDate_sec_epoch and subtracts 8 hours to create a new date CreateDateNew. The table shows how the calculation changes the (formatted) CreatedDateNew dates. In each case, the time change has also changed the date.

CreatedDate_sec_epoch CreatedDateNew
2015-11-03T06:49:25.00OZ 11/2/2015
2014-08-19T06:42:33.00OZ 8/18/2014
2014-09-28T03:12:25.00OZ 9/27/2014

Refer to the computeExpression topic for further information.