# Equivalent Function in MDX for Month(X) as in SQL

• ### Question

• Hi All,

In SQL we write conditions Month(date)=09 to get the month field from date time. I want to know the equivalent function in MDX to get the month value as an integer. I need to create a calcuated member based on the above condition. Please let me know how to achieve this.

Thanks and regards,
Ram
Wednesday, September 9, 2009 9:48 AM

• Hi Ram

I think the first statement for the AcutalDate will do the trick, please notice the MM for the month format.

CREATE MEMBER CURRENTCUBE.[MEASURES].[ACTDATENOW]
AS vba!format(now(),"MM/dd/yyyy"),
VISIBLE = 1;

YEAR([MEASURES].[ACTDATENOW]);

MONTH([MEASURES].[ACTDATENOW]);

DAY([MEASURES].[ACTDATENOW]);

Hope this helps

Tom
• Marked as answer by Wednesday, September 9, 2009 11:29 AM
Wednesday, September 9, 2009 11:12 AM

### All replies

• take alook at this

-- The First Calculated member is the value of NOW()
WITH  MEMBER [Measures].[Full Date] as 'NOW()'
-- The Second Calculated Member is the Day part of the first calculated member.
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
-- The Third Calculated Member is the Month part of the first calculated member.
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
-- The Fourth Calculated Member is the Year part of the first calculated member.
Member [Measures].[What Year] as 'YEAR([Full Date])'
SELECT
{[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS
FROM Sales
Praxy
Wednesday, September 9, 2009 9:55 AM
• Hi Praxy,

Thanks a lot for the reply. When i execute the above query it gives me wrong results

Full Date                                 What Day What Month What Year
9/9/2009 4:21:25 PM                    30                 12            1899

Please let me know where am i going wrong.

Regards,
Ram
Wednesday, September 9, 2009 10:53 AM
• Hi Ram

I think the first statement for the AcutalDate will do the trick, please notice the MM for the month format.

CREATE MEMBER CURRENTCUBE.[MEASURES].[ACTDATENOW]
AS vba!format(now(),"MM/dd/yyyy"),
VISIBLE = 1;

YEAR([MEASURES].[ACTDATENOW]);