1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @DATEPART

@DATEPART

The @DATEPART calculation function for Essbase returns a numeric representation of a date component, which can be the year, quarter, month, week, day, day of year, or day of week.

This function returns the Year/Quarter/Month/Week/Day/DayOfYear/Weekday as a number, given the input date and a date part, following the standard Gregorian calendar.

Syntax

@DATEPART ( date, date_part_ex )

Parameters

date

A number representing the input date. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use any of the following functions: @TODAY, @TODATEEX, @DATEROLL.

Date-time attribute properties of a member can also be used to retrieve this number. For example, @AttributeVal("Intro Date"); returns the product introduction date for the current product in context.

date_part_ex

Defined using the following rule:

date_part_ex ::= DP_YEAR | DP_QUARTER |DP_MONTH | DP_WEEK | DP_DAY | DP_DAYOFYEAR | DP_WEEKDAY

Defined time components as per the standard calendar:

  • DP_YEAR - Year of the input date.

  • DP_QUARTER - Quarter of the input date.

  • DP_MONTH - Month of the input date.

  • DP_WEEK - Week of the input date.

  • DP_DAY - Day of the input date.

  • DP_DAYOFYEAR - Day of year of the input date.

  • DP_WEEKDAY - Day of week of the input date.

Notes

Based on the requested time component, the output is as follows:

  • DP_YEAR returns the year of the input date in yyyy format.

  • DP_QUARTER returns the quarter of the year (1 to 4) for the input date.

  • DP_MONTH returns the month of the year (1 to 12) for the input date.

  • DP_WEEK returns the week of the year for the input date (1 to 54).

  • DP_DAY returns the day of the month (1 to 31).

  • DP_DAYOFYEAR returns the day of the year numbering (1 to 366).

  • DP_WEEKDAY returns the week day of the input date. (1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday).

Example: For June 14, 2024,

DP_YEAR returns 2024 (the year member, in yyyy format).

DP_QUARTER returns 2 (Second quarter of the year)

DP_MONTH returns 6 (Sixth month of the year)

DP_WEEK returns 24 (24th week of the year)

DP_DAY returns 14 (14th day of the month)

DP_DAYOFYEAR returns 165 (165th day of the year)

DP_WEEKDAY returns 4 (for Wednesday. Sunday = 1)

Example

Assume the outline has two date type members, MyDate1 and MyDate2.


Profit=@DateDiff(MyDate1, MyDate2, DP_WEEK);
Profit=@DatePart(MyDate1, DP_YEAR);
MyDate2=@DateRoll(MyDate1, DP_MONTH), 10);

See Also