MDX Query : Getdate()

บทความนี้จะอธิบายถึงวิธีการใช้งาน Function Getdate() ใน MDX Query
ปกติหากจะหาวันที่ปัจจุบันใน SQL Server จะใช้ Function Getdate() และจัด Format ตามต้องการ
เช่น  SELECT Convert(char(8),Getdate(),112)
แต่หากเป็นใน MDX Quey แล้ว จะแตกต่างกันไปนิดหน่อยโดยใช้ Function NOW() แทนครับ
ดูตามตัวอย่าง และนำไปประยุกต์ใช้เอาตามชอบใจ


WITH
MEMBER [Measures].[CurYear] as -- Current Year (Full)
Format(NOW(), "yyyy" )
MEMBER [Measures].[CurYearShot] as -- Current Year (With 2 digits)
Format(NOW(), "yy" )
MEMBER [Measures].[CurMonthFull] as -- Current Month (Full)
Format(NOW(),"MMMM")
MEMBER [Measures].[CurMonthNum] as -- Current Month (With 2 digits)
Format(NOW(),"MM")
MEMBER [Measures].[CurDateNum] as -- Current Date (With 2 digits)
Format(NOW(),"dd")
MEMBER [Measures].[CurDateFull] as -- Current Date
Format(NOW(),"d")
MEMBER [Measures].[CurDateFullFormat] as -- Current Date (With custom format)
Format(NOW(),"dd/MM/yyyy")
MEMBER [Measures].[PrvYear] as -- Previous Year
Format(DATEADD("yyyy",-1,NOW()),"yyyy")
MEMBER [Measures].[NxtYear] as -- Next Year
Format(DATEADD("yyyy",1,NOW()),"yyyy")
MEMBER [Measures].[PrvMonth] as -- Previous Month
Format(DATEADD("m",-1,NOW()),"MMMM")
MEMBER [Measures].[NxtMonth] as -- Next Month
Format(DATEADD("m",1,NOW()),"MMMM")
MEMBER [Measures].[PrvDate] as -- Previous Day
Format(DATEADD("d",-1,NOW()),"dd")
MEMBER [Measures].[NxtDate] as -- Next Day
Format(DATEADD("d",1,NOW()),"dd")
MEMBER [Measures].[YearDiff] as -- Difference between 2 years
DATEDIFF("yyyy",DATEADD("yyyy",-1,NOW()),DATEADD("yyyy",3,NOW()))
MEMBER [Measures].[ConvertStrToDate] as -- Convert String to Date
Cdate("1/28/2010")
select {[Measures].[CurYear],[Measures].[CurYearShot],[Measures].[CurMonthFull],[Measures].[CurMonthNum],
[Measures].[CurDateNum],[Measures].[CurDateFull],[Measures].[CurDateFullFormat],
[Measures].[PrvYear],[Measures].[NxtYear],[Measures].[PrvMonth],[Measures].[NxtMonth],
[Measures].[PrvDate],[Measures].[NxtDate], [Measures].[YearDiff],
[Measures].[ConvertStrToDate]} ON 0
FROM [Adventure Works]