Understanding query.RelativeDate in NetSuite SuiteScript 2.x

query.RelativeDate is a feature in the N/query module introduced in NetSuite 2019.1 that allows developers to define relative dates for use in query conditions. It is particularly useful when working with dynamic date ranges, enabling queries to adjust based on the current date or a specific relative time frame.

Key Features and Functionality

• Purpose: Represents a specific moment in time that can be used as a value in query conditions.
• Usage: Often used with the Query.createCondition(options) or Component.createCondition(options) methods to define dynamic date-based conditions.

Method Overview

• Supported Script Types: Works in both client scripts and server scripts.
• Module: Part of the N/query module.
• Related Members: RelativeDate Object Members.
• Introduced In: NetSuite 2019.1

Key Parameters

The Query.createRelativeDate(options) method accepts the following parameters:

1. dateId: The identifier for the relative date type (e.g., query.DateId.WEEKS_AGO, query.DateId.MONTHS_AGO).
2. value: The offset value for the relative date (e.g., 2 for 2 weeks ago).

Use Cases

1. Dynamic Reporting: Generate reports that show data from dynamic time periods, such as the last quarter or the last fiscal year.
2. Filtering Transactions: Retrieve records created within a relative date range, such as “transactions within the last 30 days.”
3. Automating Date Ranges: Build scripts that adjust date ranges automatically without manual input.

Key Notes

1. Operators: The relative date object works with specific operators from the Query.Operator enum, such as AFTER, BEFORE, and WITHIN.
2. Relative Date Ranges: Predefined ranges, like query.RelativeDateRange.THREE_FISCAL_YEARS_AGO, can be combined with custom relative dates.
3. Flexibility: Useful for building dynamic, reusable query logic for NetSuite scripts.

Syntax and Usage

To create a relative date, use the Query.createRelativeDate(options) method and include it as a value in the Query.createCondition(options) method. Below is an 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]
    });