Datetime format for dimension Please help me

Dear All,

How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00. I wan to get 01/11/2002 dd/mm/yyyy.

What I have to do. When I changed their cell value property as dd/mm/yyyy it doesnpt working ..Please to crrect my problem

with regards

Polachah

You could create a named calculation in the DSV which formats the Date column however you like and the use this as the name of the attribute.|||

Thank for replying my requirement

I did the same way but the format is not changed.. Also I tried to use that cube in a pivot grid table and tried to change the format there. Still the format is shown as yyyy/mm/dd like that..

|||

You can either break the date into pieces and join it back together however you want

Code Snippet

datename(dd,DateOrder) + '/' + convert(varchar,month(DateOrder)) + '/' + datename(yyyy,DateOrder)

But you would have to do a bit more work on the above code to get it producing a leading "0" on the day and month.

Or you can use the third parameter of the convert function that is used when converting from a datetime to a string (which I prefer to use if I can)

Code Snippet

convert(varchar,DateOrder,103)

Format 103 is dd/mm/yyyy - Books Online has a list of all the format numbers in the help for the CONVERT() function|||

Dear sir

Thank you very mcuh ... for your help .. I got from your advice what I need thank u verymuch again

DateTime and Date problem

I have created a cube with two dimensions in as2005. A time dimension and a "data" dimension. The "data" dimension is a table from a db that has a field called "ItemDate" that is of type DateTime. In the Dimension usage panel of the cube designer I create a reference in the time row to the "ItemDate" in the data dimension. The problem arises when I want to count how many "data" rows have a date of "March 27 2002". It will only count the rows with a datetime that is "3/27/2002 12:00:00 AM". All the rows that are "3/27/2002 1:45:01 PM" or anything else get ignored. Is there a way to solve this problem in analysis server 2005. Can I convert the datetime to a date or change the reference in some way.
note: I have simplified the problem and striped away names and dimensions to make it less confusing.

Assuming that the "data" dimension source is SQL Server, you can add a Named Calculation to the Data Source View, like "ItemDateOnly", defined as:

convert(datetime, convert(varchar, ItemDate, 101))

This new column could be used to join to a normal date dimension.|||Thanks, I was trying to create a calculated field in the cube with MDX. The calculated field in the data source view opens up many solutions.