Hi,

I use the following MDX statement on a MSSQl 2k server to format a
smalldatetime column in my regional (german) date format, dd.mm.yyyy:

WITH MEMBER [Measures].[TimeMemberUniqueName] AS
'[MyDate].CurrentMember.UniqueName' MEMBER [Measures].[TimeDisplayName] AS
'[MyDate].CurrentMember.Name', FORMAT_STRING='dd.mm.yyyy' SELECT
{[Measures].[TimeMemberUniqueName], [Measures].[TimeDisplayName]} ON Columns,
{[MyDate].Members} ON Rows FROM [MyTable]

This works fine in MDX Sample app (result in dd.mm.yyyy). In VisualStudio
2003, the same statement returns the original smalldatetime value in format
yyyy-mm-dd hh:mm:ss. Obviously, the Format statement is ignored. :-(

How can I influence the date format in MDX? The Dimension is a regular (not
Time!) dimension, but I tested on a Time Dimension as well - same results...

Thanks
Martin

Hi Martin,

What happens when you use VBA!Format() function, like:

'[MyDate].CurrentMember.UniqueName'
MEMBER [Measures].[TimeDisplayName] AS
'Format(CDate([MyDate].CurrentMember.Name),
"dd.mm.yyyy")'
SELECT
{[Measures].[TimeMemberUniqueName],
[Measures].[TimeDisplayName]} ON Columns,
{[MyDate].Members} ON Rows FROM [MyTable]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

thanks for your answer.

It worked nearly perfect: your statement generated dates like 6012006 for
January 6, 2006. I changed the format a bit, eventually to "dd mm yyyy", and
now it evaluates to 06.01.2006, as desired. But how does a format with blanks
generate dots between day, month and year?

Just curious, but happy :-)

Best wishes,
Martin

"Deepak Puri" wrote:

Martin,

Maybe this has to do with locale settings, but their subtleties escape
me. In the US locale, this works:

'Format(CDate("2006-02-21"), "dd.mm.yyyy")'

select {[Measures].[FmtDate]} on 0
from Sales
The result is "02.21.2006"

With a format of "dd mm yyyy", result is: "02/21/2006"

In AS 2005, "MM" is needed for month, rather than "mm"


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***