RETAIL

Syntax

Description of the illustration retail_fn.eps

Purpose

These functions return a VARCHAR2 value that uniquely identifies a member in the specified level using the 4-5-4 calendar of the National Retail Federation (NRF).

Each of the retail functions returns a VARCHAR2 value that uniquely identifies a member in an implicit hierarchy at a particular level. For this reason, the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT do not apply to these functions. They each have their own default formats. These functions respect the setting of NLS_DATE_LANGUAGE.

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 .
fmt is a text expression representing a format string. In all cases, the case-sensitivity of the format specifiers follows the same behavior as TO_CHAR. For example, MON specifies an abbreviated month in upper-case (e.g. JAN), mon specifies an abbreviated month name in lower-case (e.g. jan), and Mon specifies an abbreviated month name in title-case (e.g. Jan).
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.
nlsparam is a text expression representing an NLS parameter setting. The only currently supported parameter is NLS_DATE_LANGUAGE, and is specified in the same manner as for TO_CHAR and TO_DATE.

Formats for Retail Functions

The following table describes the format strings for each of the functions. The required formats must appear somewhere in the format string, and an error will be raised if not present. The optional formats may appear in the format string. All other format specifiers are prohibited and an error will be raised if included. The default format strings are similar to the ones for the calendar hierarchies, with years prefixed with RY. Since the retail calendar is deliberately defined by weeks starting on a Sunday, the default day format uses the day of the week and week number rather than the day and month.

Table 7-9 Formats for Retail Functions

Function	Required Formats	Optional Formats	Default Format String
`RETAIL_YEAR`	`YYYY`or `SYYYY`	None	`"RY"SYYYY` Example: `RY2024`
`RETAIL_QUARTER`	`YYYY`or `SYYYY` `Q`	None	`"Q"Q-"RY"SYYYY` Example: `Q1-RY2024`
`RETAIL_MONTH`	`YYYY`or `SYYYY` One of `MM, mon, MONTH`	`Q`	`MON-"RY"SYYYY` Example: `JAN-RY2024`
`RETAIL_WEEK`	`YYYY`or `SYYYY` `WW`	None	`"W"WW-"RY"SYYYY` Example: `W01-RY2024`
`RETAIL_DAY`	`YYYY`or `SYYYY` `WW` and one of and one of : `DY` `DAY` `DD` and one of `MM` `MON` `MONTH` `DDD`	`Q`	`DY-"W"WW-"RY"SYYYY` Example: `SUN-W01-RY2024`

Format Descriptions for Retail Functions

The following table describes the interpretation of the format specifiers for retail hierarchies. Note that all numeric formats are relative within the retail year. The year and month are as defined by the NRF 4-5-4 calendar. The capitalization rules for MON, MONTH, DY, and DAY all follow the usage elsewhere (i.e. TO_CHAR) with regard to lower-case (e.g. month => june), upper-case (e.g. MONTH => JUNE), and title-case (e.g. Month => June).

Table 7-10 Format descriptions for retail functions

Format Specifier	Description
`YYYY`	4-digit retail year (only positive years supported)
`SYYYY`	4-digit retail year, with BC years as negative
`Q`	Quarter of retail year (1-4)
`MM`	Month of retail year (1-12), corresponding to the calendar year of the retail month. For example, in the retail year 2025 the month of Feb (the 1st month in the retail year) begins on Feb 2 and ends on March 1. The `MON`/`MONTH` for all days in that month are `FEB`/`FEBRUARY`, and thus the `MM` is `02` corresponding to `Feb`. Please consult the NRF for more information on the calendar.
`MON`	Abbreviated name of month
`MONTH`	Name of month
`WW`	Week number (1-53), 2-digits. Note that because the 4-5-4 calendar results in a year with 7*52=364 days, it's occasionally necessary to add a 53rd week. Please consult the NRF for more information on the calendar.
`DY`	Abbreviated name of day
`DAY`	Name of day
`D`	Day of week (1-7). Depends on the `NLS_TERRITORY`
`DD`	Day of month (1-31), 2-digits, within the retail month. For example, retail year 2025 begins on Feb 2. Since that's the first day of the month, the DD value for that day would be 01.
`DDD`	Day of year (1-371), 3-digits, within the retail year