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 single query.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 single query.RelativeDateRange enum value, the enum value represents a range of dates, so the start and end 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 and end properties of values in the query.RelativeDateRange enum directly in the values 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]
  }); 
  
              

General Notices