Invalid Month

Question

OBIEE 12c

I am trying to incorporate the following formula in the RPD and use it so I can display employee anniversaries that are upcoming in the next 30 days:

CAST(REPLACE(CAST(MONTH("Personal Information"."Last Hire Date") AS CHAR)||'/'||CAST(DAY("Personal Information"."Last Hire Date") AS CHAR)||'/'||CAST(YEAR(CURRENT_DATE) AS CHAR),' ','') AS DATE)

OBIEE throws the Invalid Month error. Not sure why this is the case even though I have tried to eliminate cases when that column is NULL.

Thomas Dodds · Answer

True ... actually a well formed date (calendar) dimension takes care of this issue once and for all ...

asim cholas · Answer

Day of year could be different in leap year. Include a case statement like below  to validate if you are using day of year formula.

case when (MOD(year(Dim_Date.DATE), 4)=0 and MOD(year(Dim_Date.DATE), 100)<>0 ) OR (MOD(year(Dim_Date.DATE), 4)=0 and MOD(year(Dim_Date.DATE), 100)=0 and  MOD(year(Dim_Date.DATE), 400)=0)

asim cholas · Answer

timestampdiff(SQL_TSI_DAY,CURRENT_DATE,timestampadd(SQL_TSI_YEAR,(timestampdiff(SQL_TSI_YEAR,"Personal Information"."Last Hire Date",CURRENT_DATE)),"Personal Information"."Last Hire Date"))

apply 0 to 30 filter.

Thanks

Aj