Skip to Main Content

Problem with OBIEE Date Conversion

user10979563Nov 26 2009 — edited Nov 26 2009

We are using a date conversion expression in OBIEE logical layer to get the month end date for any given date. We are using the following expression to achieve this:

TimestampAdd(SQL_TSI_DAY, -1, TimestampAdd(SQL_TSI_MONTH, 1, cast(left('01' || right(cast(<Date Column> as CHARACTER ( 30 )) , 28) , 9) as DATE ) ))

The datatype of this column is DATE

The corresponding SQL (Oracle) expression generated is like this:

ADD_MONTHS( cast(substr(concat('01', substr(cast(<Date Column> as CHARACTER ( 30 ) ) , -(28))) , 1, 9) as DATE), 1) + -1 )

The date is returned in the format DD-MON-YY

Now the problem is that in the Answers reports the result column is interpreted wrongly. For example, if the value returned is 23-NOV-09, it is taken as 23-NOV-0009 instead of 23-NOV-2009 in Answers reports. But the database query gives the correct result.

To make matters more confusing the same rpd is giving correct results in reports in some environments while the results are wrong in other environments.

Can someone help me to solve this issue.