Need help in converting date time to date in Mdx query.

Question

Hello,

this is my query and results
select
{ [Measures].[Transaction Amount]
, [Measures].[Transaction dbo Count]} on 0,
NON EMPTY
{([Transaction Type].[Transaction Type].[Transaction Type].MEMBERS,
[Transaction Type].[Transaction Type Description].[Transaction Type Description].MEMBERS,
[Transaction Sub Type].[Transaction Sub Type].[Transaction Sub Type].MEMBERS,
[Transaction Sub Type].[Transaction Sub Type Description].[Transaction Sub Type Description].MEMBERS,
[Transaction dbo].[Posting Date].[Posting Date].MEMBERS

)} ON 1
from mycube
where ({[Transaction Type].[Hierarchy].&[24],
[Transaction Type].[Hierarchy].&[53],
[Transaction Type].[Hierarchy].&[59],
[Transaction Type].[Hierarchy].&[60],
[Transaction Type].[Hierarchy].&[103],
[Transaction Type].[Hierarchy].&[134]})

I want to convert or see the datetime column as only date value. I don't need timestamp. How can I do that in mdx query? Can anyone please guide me or help me here?

Thanks,
KK

Answer 1

I want to convert or see the datetime column as only date value.

The best bet is to convert it already on datasource side / in datasource view, but you provided to less on information for a more detailed answer.
What for a type of datasource do you have?

Answer 2

Hi @KK ,

Maybe you can get some inspiration from these links:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/15601dce-59ad-49f2-b678-7b1547543352/mdx-query-for-date-format?forum=sqlanalysisservices
https://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

You can't use MDX to change the text of dimension members' captions. This needs to be done when setting up the dimension. Note that by simply dropping the time part, you'll be changing the granularity of the attribute. One possibility is to set up an attribute with date-time as key and date as name.