Wave Analytics SAQL Reference

SAQL Overview
Enable SAQL Logs in the Browser
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
Math Functions
coalesce()
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

This table lists SAQL date functions:
Date Function Description
date(year, month, day) Returns a date. Specify three dimensions of a date in the following order: year, month, day. For example: 
1date('OrderDate_Year', 'OrderDate_Month', 'OrderDate_Day')
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: 
1dateRange([1970, 1, 1], [1970, 1, 31])
daysBetween(date1, date2) Returns the number of days between two dates as an integer.
The daysBetween() function can’t take dimensions as arguments directly. Pass toDate() and now(​) functions as arguments. 
1q = foreach q generate daysBetween(​toDate(​OrderDate, “yyyy-MM-dd”​),
2   now()) as daysToShip;
1q = foreach q generate daysBetween(​​toDate(​​OrderDate, “yyyy-MM-dd”​​),
2   toDate(​​ShipDate, “yyyy-MM-dd”​​)) as daysToShip;
1q = foreach q generate daysBetween(​toDate(​OrderDate_​Year + “:” 
2   + OrderDate_​Month + “:” + OrderDate_​Day, “yyyy:MM:dd”​), toDate(​ShipDate_​Year + “:” 
3   + ShipDate_​Month + “:” + ShipDate_Day,​ “yyyy:MM:dd”​)) as daysToShip;
now() Returns current datetime in UTC (GMT). This function is valid in a foreach statement only.
1q = foreach q generate now() as now;

This function is commonly used in daysBetween(​) and toString() functions.
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.
1q = foreach q generate toDate(​OrderDate);
1q = foreach q generate toDate(​OrderDate_​Day + \"-\" +​ OrderDate_​Month + \"-\" + ​OrderDate_​Year, \"dd-MM-yyyy\​");

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_sec) returns '1970-01-01 00:00:00'.
toString(date, formatString) Converts a date to a string.
This function must take a toDate() or now(​) function as its first argument. 
1q = foreach q generate toString(now(​), \"yyyy-MM-dd HH:mm:ss\") as ds1;

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

1a = filter a by date('year', 'month', 'day') in [dateRange([1970, 1, 1], [1970, 1, 11])];

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:

1a = filter a by date('year', 'month', 'day') in ["1 year ago".."current year"];
2a = filter a by date('year', 'month', 'day') in ["2 quarters ago".."2 quarters ahead"];
3a = filter a by date('year', 'month', 'day') in ["4 months ago".."1 year ahead"];
4a = filter a by date('year', 'month', 'day') in ["2 fiscal_years ago".."current day"];
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 FiscalMonthOffeset 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 FiscalMonthOffeset 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: 
1a= filter a by date('year', 'month', 'day') in ["current day - 1 year"..] ;
In this query, the start date is 2014-12-16 00:00:00 and the end date is 2017-3-31 23:59:59:
1a= filter a by date('year', 'month', 'day') in ["current day".."2 years ahead + 3 months"];
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:
1a= filter a by date('year', 'month', 'day') in ["current fiscal_year + 5 days".."2 years ahead + 3 months"];

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. 
1a = filter a by date('year','month','day') in [.."current month"];

Example

This example shows an open-ended fixed date range. The date format of OrderDate is yyyy-MM-dd.
1q = filter q by OrderDate in [“2015-01-01”​..];