Hi

I am getting very odd results when following mdx is executed. I have run this on two different SQL server 2012 with regional settings as US and other sql server 2012 with regional settings as UK.

WITH MEMBER

member [LinkTimeAdd11Date_Manual] as dateadd("d",15,"11/12/98")

member [LinkTimeAdd15Date_Manual] as dateadd("d",15,"13/12/98")

SELECT

 [LinkTimeAdd11Date_Manual]

,[LinkTimeAdd15Date_Manual]

}

ON COLUMNS

FROM

[AnyCubeName]

Result I get is:

[LinkTimeAdd11Date_Manual]: 11/27/1998 (i.e. date is in November)

[LinkTimeAdd15Date_Manual]: 12/28/1998 (i.e. date is in December)

It is observed that dates having first two digit between 1 to 12 is identified as month  i.e. mm/dd/yy format but first two digit between 13 to 31 is identified as day i.e. dd/mm/yy.

Is their any way that the SSAS stops using auto-detection and stick to one of the format (my requirement is mm/dd/yy).

Nikesh Shah

Hi Nikesh,

This behavior is by default. The US date format is "mm/dd/yy", and UK date format is "dd/mm/yy". We should use the unified date format in the MDX query, for example:

WITH MEMBER  [LinkTimeAdd11Date_Manual] as Format(dateadd("d",15,"12/11/98"),"MM/dd/yyyy")
member [LinkTimeAdd15Date_Manual] as Format(dateadd("d",15,"12/13/98"),"MM/dd/yyyy")
SELECT
{ [LinkTimeAdd11Date_Manual]
,[LinkTimeAdd15Date_Manual]
}
ON COLUMNS 
FROM [Adventure Works]

We can use Format() function to get expected date format. 

If you have any feedback on our support, please click here.

Best Regards,

Hi Elvis

Let me re-frame my issue:

I have SQL 2012 server with UK regional settings.

When I run the above query, it gave me the result as

[LinkTimeAdd11Date_Manual]: 11/27/1998 (i.e. date is in November, whereas expected date result was 26 of November 1998 )

[LinkTimeAdd15Date_Manual]: 12/28/1998 (i.e. date is in December, whereas expected date result is same as expected i.e. 28th December 1998)

Why is it that 11th Dec is treated as 12th of Nov so that 15 days added gives 27th Nov.

Why is it that 13th Dec is treated as 13th Dec so that 15 days added gives 28th Dec.

When I have run the same query on SQL 2008 and SQL 2005 with UK regional settings on same domains, I am getting valid results i.e. 26th Dec and 28th Dec as expected.

When we executed the same query on SQL 2005 and SQL 2008, it gave the results same as expected.

Is it possible to stop this auto-intelligence of Date conversion of SQL 2012 i.e. first two digit is date between 1 to 12, it is treated as month but if two digit is between 13 to 31 it is treated as day.

I am doing migration of SSAS 2005 to SSAS 2012. The original MDX queries are fully functional for 6 years in SSAS 2005. This MDX queries are very complex and has calls calculated measures, hence would not like to amend the original query. So during migration from SSAS 2005 to SQL 2012 we expected it to work without any modifications.

We have investigated the issue and narrow down to a simplified form of the MDX as provided so as to present the issue in the forum.

Any help is appreciated.

Nikesh Shah