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 is a list of SAQL date functions.

date(`year`, `month`, `day`)

Returns a date. Specify 3 dimensions of a date in the following order: year, month, day. For example:

1date('OrderDate_Year', 'OrderDate_Month', 'OrderDate_Day')

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:

1q = load \"em/dates\";
2q = foreach q generate date_diff("year", toDate(DateOfBirth, "yyyy-MM-dd"), now()) as age;
3q = order q by age asc;

Invalid examples:

1q = group q by date_diff("month", toDate(DateOfBirth, "yyyy-MM-dd"),
2      toDate(RegisteredDate));

1q = order q by date_diff("year", toDate(DateOfBirth, "yyyy-MM-dd"),
2      toDate(RegisteredDate));

1q = filter q by date_diff("day", toDate(DateOfBirth, "yyyy-MM-dd"), now());

date_to_epoch(`date`)

Converts a date to epoch seconds. This is the reverse of the toDate(epoch_seconds) function.

Returns the number of seconds elapsed since January 1, 1970, 00:00:00.000 GMT. If a date before this is passed, the result will be a negative number. If the parameter is not a date, an error results. If null is passed as a parameter, null is returned.

Examples:

1date_to_epoch(now()) == 1496404452 (current time)

When supplying a date, first use the toDate() function to format the date correctly.

1date_to_epoch(toDate("2017-06-02 11:54:12")) == 1496404452

date_to_string(`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 date_to_string(now(), \"yyyy-MM-dd HH:mm:ss\") as ds1;

Replaces (and is functionally identical to) the soon-to-be deprecated toString() function.

Note

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])

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:

1q = foreach q generate day_in_week(toDate(OrderDate));

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.

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;

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:

1q = load \"em/dates\";
2q = foreach q generate month_days(toDate(BillDate, "yyyy-MM-dd")) as BillingMonth;
3q = order q by BillingMonth asc;

Invalid examples:

1q = group q by month_days(toDate(BillDate, "yyyy-MM-dd"));

1q = order q by month_days(toDate(BillDate, "yyyy-MM-dd"));

1q = filter q by month_days(toDate(BillDate, "yyyy-MM-dd"));

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.

1q = foreach q generate now() as now;

This function is commonly used in daysBetween() and toString() functions.

quarter_days(`date`)

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.

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_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.

1q = foreach q generate toString(now(), \"yyyy-MM-dd HH:mm:ss\") as ds1;

This function will soon be deprecated. Please use the functionally-identical date_to_string() function instead.

Note

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:

1q = load \"em/dates\";
2q = foreach q generate week_last_day(toDate(BillDate, "yyyy-MM-dd")) as BillingWeek;
3q = order q by BillingWeek asc;

Invalid examples:

1q = group q by week_last_day(toDate(BillDate, "yyyy-MM-dd"));

1q = order q by week_last_day(toDate(BillDate, "yyyy-MM-dd"));

1q = filter q by week_last_day(toDate(BillDate, "yyyy-MM-dd"));

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

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 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:

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"..];

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.

1"Extract_Opportunity": {
2  "action": "computeExpression",
3  "parameters": {
4    "source": "Digest_Opportunity",
5    "mergeWithSource": true,
6    "computedFields": [
7     {
8      "name": "CreatedDateNew",
9      "type": "Date",
10      "format": "MM/dd/yyyy",
11      "saqlExpression": "toDate(CreatedDate_sec_epoch - 8*3600)"
12     }
13    ]
14  }
15},

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.

Analytics SAQL Reference

Date Functions

Functions

date(year, month, day)

date_diff(datepart,startdate,enddate)

date_to_epoch(date)

date_to_string(date, formatString)

dateRange(startArray_y_m_d, endArray_y_m_d)

day_in_month(date)

day_in_quarter(date)

day_in_week(date)

day_in_year(date)

daysBetween(date1, date2)

month_days(date)

month_last_day(date)

now()

quarter_days(date)

quarter_last_day(date)

toDate(string [,formatString])

toDate(epoch_seconds)

toString(date, formatString)

week_last_day(date)

year_days(date)

year_last_day(date)

Specify Fixed Date Ranges

Example

Specify Relative Date Ranges

Add and Subtract Dates

Example

Use Open-Ended Relative Date Ranges

Example

Example

Working with Time Zones

date(`year`, `month`, `day`)

date_diff(`datepart,startdate,enddate`)

date_to_epoch(`date`)

date_to_string(`date`, `formatString`)

dateRange(`startArray_y_m_d`, `endArray_y_m_d`)

day_in_month(`date`)

day_in_quarter(`date`)

day_in_week(`date`)

day_in_year(`date`)

daysBetween(`date1`, `date2`)

month_days(`date`)

month_last_day(`date`)

quarter_days(`date`)

quarter_last_day(`date`)

toDate(`string` [,`formatString`])

toDate(`epoch_seconds`)

toString(`date`, `formatString`)

week_last_day(`date`)

year_days(`date`)

year_last_day(`date`)