Calendar Dates

[goro2010]

Hi Everyone,

I need some help, I have made my calendar but all my tabs show empty, this is causing that no dates are shown as YEAR / MONTH ect.

Here is my script part:

"Calendar":

LOAD [TrimDate],

     Day([TrimDate])                                                                                                     AS [Day],

     WeekDay([TrimDate])                                                                                                 AS [WeekDay],

     Week([TrimDate])                                                                                                     AS [WeekOfYear],

     WeekYear([TrimDate])                                                                                                 AS [WeekYear],        

     Month([TrimDate])                                                                                                     AS [Month],

     Year([TrimDate])                                                                                                     AS [Year],

     Month(Date([TrimDate],'DD/MM/YYYY'))                                                                                AS [Month Name],

     Date(MonthStart([TrimDate]),'MMM/YY')                                                                                 AS [MonthFiscal],

     Date(YearStart([TrimDate],0,0),'YYYY')                                                                                AS [YearFiscal],

     Date([TrimDate],'MM-YYYY')                                                                                         AS [YearMonth],

     Year([TrimDate])*100+Month([TrimDate])                                                                                    AS [YearMonth2],  

     'Q' & Ceil(Month([TrimDate])/3)                                                                                    AS [Quarter],

     Year([TrimDate]) & '/' & 'Q' & Ceil(Month([TrimDate])/3)                                                                AS [YearQuarter],

     Num(WeekDay([TrimDate]))                                                                                             AS [Weekday#],

     YearToDate([TrimDate], 0,1)                                                                                         AS [CurrentYTDFlag],

     YearToDate([TrimDate],-1,1)                                                                                         AS [LastYTDFlag],

     If(Mod(Date(YearStart([TrimDate],1,0),'YYYY'),4) = 0 AND Mod(Date(YearStart([TrimDate],1,0),'YYYY'),100) <> 0

      OR Mod(Date(YearStart([TrimDate],1,0),'YYYY'),400) = 0,'Yes','No')                                                AS [LeapYearFlag],

     If(Year([TrimDate]) = (Year(Today())), 1, 0)                                                                         AS [CY_Flag],

     If(Year([TrimDate]) = (Year(Today())-1), 1, 0)                                                                     AS [LY_Flag]

RESIDENT  "V_OBJ_Event3"

;

Here is my trimming part:

"V_OBJ_Event2":

LOAD [EventDateTime],

     Trim(date(date([EventDateTime],'YY/MM/DD hh:mm:ss[.fff] TT'),'DD/MM/YYYY')) as [TrimDate]

RESIDENT  "V_OBJ_Event";

"V_OBJ_Event3":

NoConcatenate

LOAD *

RESIDENT "V_OBJ_Event";

LEFT JOIN ("V_OBJ_Event3")

LOAD *

RESIDENT "V_OBJ_Event2";

DROP TABLES "V_OBJ_Event2", "V_OBJ_Event";

Any help would be appricated.

Thanks

[er_mohit]

date#(date([EventDateTime],'YY/MM/DD hh:mm:ss[.fff] TT'),'DD/MM/YYYY') as [TrimDate]

[Gysbert_Wassenaar · Accepted solution]

If you use the trim function you create a string. So you need to use the date# function to make a date from the string again.

If you only want to get rid of the time part of the date then use floor(EventDateTime) as TrimDate

[kaushiknsolanki]

It should be

date(date#([EventDateTime],'YY/MM/DD hh:mm:ss[.fff] TT'),'DD/MM/YYYY') as [TrimDate]

Regards,

Kaushik Solanki

[goro2010]

Thanks a lot! That worked.