Archive

Posts Tagged ‘date formats’

MDX – Date Format

January 7, 2012 1 comment

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

Categories: MDX Tags: