DatePart
The MDX DatePart function for Essbase returns a numeric representation of a date component.
This function returns the Year/Quarter/Month/Week/Weekday/DayOfYear/Day as a number, given the input date and a date part, following the standard Gregorian calendar.
Syntax
DatePart ( date, date_part_ex )
Parameters
- date
-
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(), DateRo
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.
-
- date_part_ex
-
Defined time components as per the standard calendar.
-
DP_YEAR - Year of the input date, in
yyyy
format. -
DP_QUARTER - Quarter of the year (1 to 4) for the input date.
-
DP_MONTH - Month of the year (1 to 12) for the input date.
-
DP_WEEK - Week of the year for the input date (1 to 54).
-
DP_WEEKDAY - Week day of the input date. (1 - Sunday, 2 - Monday, ... 7 - Saturday).
-
DP_DAYOFYEAR - Day of the year numbering (1 to 366).
-
DP_DAY - Day of the month for the input date (1 to 31).
-
Notes
Based on the requested time component, the output is as follows:
-
DP_YEAR returns the year of the input date in
yyyy
format. -
DP_QUARTER returns the quarter of the year (1 to 4) for the input date.
-
DP_MONTH returns the month of the year (1 to 12) for the input date.
-
DP_WEEK returns the week of the year for the input date (1 to 54).
-
DP_WEEKDAY returns the week day of the input date. (1 - Sunday, 2 - Monday, ... 7 - Saturday).
-
DP_DAYOFYEAR returns the day of the year numbering (1 to 366).
-
DP_DAY returns the day of the month for the input date (1 to 31).
Example: For June 14, 2005,
DP_YEAR
returns 2005 (the year member, in yyyy
format).
DP_QUARTER
returns 2 (Second quarter of the year)
DP_MONTH
returns 6 (Sixth month of the year)
DP_WEEK
returns 24 (24th week of the year)
DP_WEEKDAY
returns 4 (for Wednesday. Sunday = 1)
DP_DAYOFYEAR
returns 165 (165th day of the year)
DP_DAY
returns 14 (14th day of the month)
Example
The following query returns the quarterly sales for the second quarter across all years for the product Cola in the market California.
SELECT
{[Sales]}
ON COLUMNS,
{
Filter(
[Time dimension].Quarters.members,
Datepart(
getFirstDate([Time dimension].CurrentMember),
DP_QUARTER
) = 2
)
}
ON ROWS,
FROM MySamp.Basic
WHERE (Actual, Cola, California);