RETAIL_ADD_X_PERIODS

Syntax


Description of retail_add_x_periods_fn.eps follows
Description of the illustration retail_add_x_periods_fn.eps

Purpose

These functions add (or subtract if interval is negative) a given number of periods from the given date based on the Gregorian calendar.

  • dtexpr is a datetime expression with one of the following types (or any value that can be implicitly converted to DATE): DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE .

  • The num_periods parameter is an integer that when positive will add periods and when negative will subtract periods.

  • All retail functions take an optional is_restated parameter that controls how years that require 53 weeks are handled.

    The is_restated parameter is a text expression that must resolve to either RESTATED or NOT RESTATED (case-insensitive). A retail year typically contains exactly 52 weeks, which is 364 days. Therefore, every 5 or 6 years an extra 53rd week is added. This is a non-restated calendar and is what is produced when the is_restated parameter is NOT RESTATED (the default). Some users prefer to have all years contain 52 weeks for comparison purposes. Such calendars are called restated calendars, and is what is produced when the is_restated parameter is RESTATED.

RETAIL_ADD_YEARS adds or subtracts the given number of years, where a year is always defined as a period of 12 months.

RETAIL_ADD_QUARTERS adds or subtracts the given number of quarters, where a quarter is always defined as a period of 3 months.

RETAIL_ADD_MONTHS This is defined similarly as the FISCAL_ADD_MONTHS . This will return the corresponding day (by position) within the target month, and follow the same rule as above where if the given date is the last day of the month or if the resulting month has fewer days, the last day of the resulting month is used.

RETAIL_ADD_WEEKS adds or subtracts the given number of weeks, where a week is always defined as a period of 7 days.

RETAIL_ADD_DAYS simply adds or subtracts the given number of days.