Monthly Archives: December 2009

Equivalent “Next_Day” Oracle’s functionality in OBIEE

Standard

 

There is equivalent functionality in OBIEE, for Next_Day function in Oracle. 

          There is a question too in Oracle forums by me. And, excellent answer by mma1709 (Sorry to say this, as i don’t know his name and that is what there in forums too)

Here it’s…

TIMESTAMPADD(SQL_TSI_DAY, ( 7 – dayofweek(Time.ActualDate) + X ), Time.ActualDate) 

 

X = 1 will be sunday. If you want tuesday it will be 3. In that way, X will be replaced by the number depends on the next dayname you want.

Conversion of date datatype to timestamp in OBIEE

Standard

Hi,

In OBIEE, direct date to timestamp conversion is not possible unless we use evaluate functions to cal l it’s database functions…

One way i found to convert date column to it’s timestamp is:

First, cast the date column to char later cast it to timestamp

Ex: Cast(Cast(CURRENT_DATE AS CHAR) AS TIMESTAMP)

  

You may have question why we need to convert it to timestamp..

Well, while using timestampdiff with sql_tsi_hour as interval, we can’t pass date datatype columns as it throws error:

Ex: TimestamDiff(SQL_TSI_HOUR, TimeID, CURRENT_DATE) won’t take as functionality. Gives you following error:

[nQSError: 10058] A general error has occurred. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000)

 Now, try in this way:

TimestamDiff(SQL_TSI_HOUR, TimeID, Cast(Cast(CURRENT_DATE as Char) as Timestamp))

It gives you proper results with out any error.

Thanks

Kishore Guggilla