Invalid Month

User_ZZQJ0

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.

Syedsalmancs110

When you use the cast function to transform a character data type as a date, you might face issue as reported because date format that you have to give in a formula column depends on the configuration.

Give below blog a read for more information on same:

https://gerardnico.com/wiki/dat/obiee/obis/cast_as_date

Thomas Dodds

Simply build a logical column that evaluates the actual date physical column ... no need to then be casting and concatenating -- let the database handle the data!

CASE WHEN  to_char("Personal Information"."Last Hire Date",'DDD') between to_char(sysdate,'DDD') and to_char(sysdate+30,'DDD') THEN 'Y' ELSE 'N' END

DDD - is day of the year 1-366 ... so if the day of the year for the hire date is in the next 30 days worth of days of the year, then you get a 'Y' ....

Then just use that column as your filter column to display employees with an upcoming anniversary ...

asim cholas

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

asim cholas

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)

Thomas Dodds

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