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.
