Home > MDX > MDX – Date Format

MDX – Date Format

One of my SQL Server motivator (not presenting the details) wrote a reply mail to me with a message as

I am very impressed the way he wrote the date in TSQL, so I planned to write the same kind of date format in MDX.
In the process of making the date format, I realised this will be a useful information while writing MDX queries.
Here documenting some useful basic date formats using MDX.

WITH  MEMBER [Measures].[TodayDate] AS NOW()
      MEMBER [Measures].[DateDDMMYYYY] AS format(NOW(), "ddMMyyyy")
	  MEMBER [Measures].[DatePartFormat] AS format(NOW(), "dd")
	  MEMBER [Measures].[DatePart] AS DATEPART("d",NOW())
      MEMBER [Measures].[MonthPartFormat] AS format(NOW(), "MM")
	  MEMBER [Measures].[MonthPart] AS DATEPART("M",NOW())
	  MEMBER [Measures].[YearPart] AS format(NOW(), "yyyy")
      MEMBER [Measures].[DateADD] AS DATEADD("D",2,cdate(NOW()))
	  MEMBER [Measures].[MonthADD] AS format(DATEADD("M",2,cdate(NOW())),"dd-MM-yyyy")
	  MEMBER [Measures].[YearADD] AS format(DATEADD("YYYY",2,cdate(NOW())),"yyyy/MM/dd")
	  
SELECT
   {[TodayDate],[DateDDMMYYYY],[DatePartFormat],[DatePart],[MonthPartFormat]
   ,[MonthPart],[YearPart],[DateADD],[MonthADD],[YearADD]} ON COLUMNS
FROM [Adventure Works]


Points to consider:
1. Presented only a basic date formats, thought we could do any complex date format using these formats.
2. Planning to update the post whenever I get an interesting yet useful date format

Advertisements
Categories: MDX Tags:
  1. August 4, 2013 at 2:36 am

    Thanks in favor of sharing such a pleasant thought, post
    is pleasant, thats why i have read it entirely

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: