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

@DATEDIFF

The @DATEDIFF calculation function for Essbase returns the difference between two input dates.

This function returns the difference (number) between two input dates in terms of the specified date-parts, following a standard Gregorian calendar.

Syntax

@DATEDIFF ( date1, date2, date_part )

Parameters

date1

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.

date2

A second input date. See date1.

date_part

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 input date_part, the difference between the two input dates is counted in terms of time component specified.

Example: For input dates June 14, 2024 and Oct 10, 2025,

  • DP_YEAR returns the difference in the year component (2025 - 2024 = 1).

  • DP_QUARTER returns the distance between the quarters capturing the input dates (Quarter 4, 2025 - Quarter 2, 2024 = 6).

  • DP_MONTH returns the distance between the months capturing the input dates (Oct 2025 - June 2024 = 16).

  • DP_WEEK returns the distance between the weeks capturing the input dates. Each Standard calendar week is defined to start on Sunday, and it spans 7 days (Oct 10, 2025 - June 14, 2024 = 69).

  • DP_DAY returns the difference between the input dates in terms of days (483 days).

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