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 )

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(), GetFirstDate(), GetLastDate(), DateRoll().

Date-time attribute properties of a member can also be used to retrieve this number. For example,

  • Product.currentmember.[Intro Date] returns the product introduction date for the current product in context.
  • [Cola].[Intro Date] returns the product introduction date for Cola.
date2 A second input date. See date1.
date_part 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.

Description

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,

Example

The following query returns weekly sales for the last 6 months for the product Cola in the market California.

SELECT 
{sales} ON COLUMNS,
Filter(
  [Time dimension].Weeks.members, 
  Datediff(
    GetFirstDate([Time dimension].CurrentMember),
        Today(),
        DP_MONTH
   ) < 6
 )
ON ROWS
FROM Mysamp.Basic
WHERE (Actual, California, Cola);