Conversion of date datatype to timestamp in OBIEE

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.