将日期格式更改为较短的日期 [英] Changing a date format to a shorter date

问题描述

我们有以下脚本:

WITH 
SET [Last56Days] as
 TAIL ( [Date].[Date - Calendar Month].[Calendar Day].members, 56 )
MEMBER [Measures].[DateValue] as
 [Date].[Date - Calendar Month].CURRENTMEMBER.member_value, format_string = "short date"
MEMBER [Measures].[DateValue2] as
 [Date].[Date - Calendar Month].CURRENTMEMBER.member_value, format_string = "dd/mm/yyyy"
SELECT
    { [Measures].[DateValue], [Measures].[DateValue2]} ON COLUMNS,
    Hierarchize ({ [Last56Days] } ) ON ROWS
FROM [Our Cube]

它返回这个:

我可以以某种方式更改日期格式，使日期像这样2014 年 2 月 9 日"，即更短吗?

Can I change the date format somehow so that the dates are like this "09 Feb 2014" i.e. shorter ?

推荐答案

根据评论，我认为问题在于您有整数日期键和名称列，它在 Analysis Services 中始终是字符串类型.这些不适用于需要日期的日期格式(或包含自 1900 年 1 月 1 日以来的日期的双精度数，以及一天中的时间的分数，即 8:30 am 将是 8.5/24).

From the comments I suppose that the issue is that you have integer date keys and a name column which is always of type string in Analysis Services. These will not work with date formats which need dates (or doubles containing the days since January, 1, 1900, and as fractions the time of day, i. e. 8:30 am would be 8.5/24).

因此，您可以使用

MEMBER [Measures].[Date as int] as
       [Date].[Date - Calendar Month].CURRENTMEMBER.Properties('Key0', Typed)
MEMBER [Measures].[Date Year] as
       Fix([Measures].[Date as int] / 10000)
MEMBER [Measures].[Date Month] as
       Fix(([Measures].[Date as int] - [Measures].[Date Year] * 10000) / 100)
MEMBER [Measures].[Date Day] as
       [Measures].[Date as int] - [Measures].[Date Year] * 10000 - [Measures].[Date Month] * 100
MEMBER [Measures].[DateValue] as
       // convert it to Date data type and use a format string on that:
       DateSerial([Measures].[Date Year], [Measures].[Date Month], [Measures].[Date Day]),
       format_string = 'dd/mm/yyyy'

请参阅属性的文档和 VBA 函数列表，如 Fix 和 日期序列.

See the documentation of Properties and the list of VBA functions like Fix and DateSerial.

看我上面代码的复杂度，用字符串逻辑代替整数逻辑可能更好，i.e.省略 Properties 的 typed 参数，然后使用 Left、Mid 和 计算年、月和日code>Right 并将其转换为整数.但是这些细节留给读者作为练习".

Looking at the complexity of my above code, it may be better to use string logic instead of integer logic, i. e. omitting the typed argument to Properties and then calculating year, month, and day using Left, Mid, and Right and converting that to integers. But "the details of that are left as an exercise to the reader".

这篇关于将日期格式更改为较短的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！