Essbase @DATEDIFF

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 )
Parameter Description
date1 A number representing the input date between January 1, 1970 and Dec
31, 2037. 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.

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, 2005 and Oct 10, 2006,

  • DP_YEAR returns the difference in the year component. (2006
    – 2005 = 1)
  • DP_QUARTER returns the distance between the quarters capturing
    the input dates. (Quarter 4, 2006 – Quarter 2, 2005 = 6)
  • DP_MONTH returns the distance between the months capturing
    the input dates. (Oct 2006 – June 2005 = 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, 2006 – June 14, 2005 = 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

  • @DATEPART
  • @DATEROLL
  • @FORMATDATE
  • @TODATEEX
  • @TODAY