5 Replies Latest reply on Jul 5, 2017 12:13 PM by Thomas Dodds

    Invalid Month

    3208479

      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.

        • 1. Re: Invalid Month
          Syed Hamd Salman

          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

          • 2. Re: Invalid Month
            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 ...

            • 3. Re: Invalid Month
              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

              • 4. Re: Invalid Month
                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)

                • 5. Re: Invalid Month
                  Thomas Dodds

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