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

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