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

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

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

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);