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,
|
date2 | A second input date. See date1. |
date_part | Defined time components as per the standard calendar.
|
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);