Relative Dates in the N/query Module
You can use relative dates when you create query conditions. The query.RelativeDate object represents a specific date or moment in time relative to the current date. Each of the values in the query.RelativeDateRange enum represents a range of dates relative to the current date. When you use a query.RelativeDate object or query.RelativeDateRange enum value to create a query condition, make sure that you use an operator that makes sense for the relative date that you provide to Query.createCondition(options) or Component.createCondition(options). The query.Operator enum contains the supported operators for the N/query module, but not all operators apply to relative dates. Use the following operators with relative dates:
-
AFTER
-
AFTER_NOT
-
BEFORE
-
BEFORE_NOT
-
ON
-
ON_NOT
-
ON_OR_AFTER
-
ON_OR_AFTER_NOT
-
ON_OR_BEFORE
-
ON_OR_BEFORE_NOT
-
WITHIN
-
WITHIN_NOT
When you create a query condition using the WITHIN
or WITHIN_NOT
operators and a query.RelativeDate
object, the condition uses the current date as one of the boundaries of the date range. For example, consider the following query.RelativeDate
object that represents a date two days before the current date:
var myDatesAgo = query.createRelativeDate({
dateId: query.DateId.DAYS_AGO,
value: 2
});
You can use this myDatesAgo
object when you create a query condition. Consider the following query condition that is created using the WITHIN
operator and this myDatesAgo
object:
var myCondition = myQuery.createCondition({
fieldId: 'trandate',
operator: query.Operator.WITHIN,
values: myDatesAgo
});
This query condition matches dates that are between two days ago and the current date (the day before yesterday, yesterday, and today).
Conversely, consider the following query.RelativeDate
object that represents a date two days after the current date:
var myDatesFromNow = query.createRelativeDate({
dateId: query.DateId.DAYS_FROM_NOW,
value: 2
});
If you create a query condition using the WITHIN
operator and this myDatesFromNow
object, the condition matches dates that are between the current date and two days from now (today, tomorrow, and the day after tomorrow).
You can use the query.RelativeDate
object, the query.RelativeDateRange enum, and the WITHIN
operator to specify complex date ranges. You can do this in several ways:
-
Use a single
query.RelativeDate
object or query.RelativeDateRange enum value. When you use a singlequery.RelativeDate
object, the object represents a specific moment in time, so the current date is used automatically as one of the boundaries. When you use a singlequery.RelativeDateRange
enum value, the enum value represents a range of dates, so thestart
andend
properties of the date range are used automatically as the boundaries. For example:var myComplexCondition = myQuery.createCondition({ fieldId: 'trandate', operator: query.Operator.WITHIN, values: query.RelativeDateRange.SAME_DAY_LAST_WEEK });
In this example, the first boundary is the beginning of the same day last week, and the second boundary is the end of the same day last week. Using
query.RelativeDateRange.SAME_DAY_LAST_WEEK
is equivalent to using either of the following:-
query.RelativeDateRange.SAME_DAY_LAST_WEEK.interval
-
[query.RelativeDateRange.SAME_DAY_LAST_WEEK.start, query.RelativeDateRange.SAME_DAY_LAST_WEEK.end]
-
-
Use the
start
andend
properties of values in the query.RelativeDateRange enum directly in thevalues
parameter for Query.createCondition(options) or Component.createCondition(options). For example:var myComplexCondition = myQuery.createCondition({ fieldId: 'trandate', operator: query.Operator.WITHIN, values: [query.RelativeDateRange.THIS_FISCAL_YEAR.start, query.RelativeDateRange.YESTERDAY.end] });
-
Use a combination of query.RelativeDateRange enum values and custom
query.RelativeDate
objects. For example:var myEndDate = query.createRelativeDate({ dateId: query.DateId.WEEKS_AGO, value: 2 }); var myComplexCondition = myQuery.createCondition({ fieldId: 'trandate', operator: query.Operator.WITHIN, values: [query.RelativeDateRange.THREE_FISCAL_YEARS_AGO.start, myEndDate] });