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)
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.
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.