Conversion of date datatype to timestamp in OBIEE



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



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.


Kishore Guggilla


About Kishore & Naresh

Hi.. This is a blog operated by Kishore and Naresh. We are colleagues and working as BI Developers. Here we want to share our ideas and researches here. We were the active members of OTN Forums and inspired with Venkat and John's blog. We may not reach to that level, but we brings up all our ideas and research work we done. All the views expressed here are our own and does not reflect the views of our employers. We need all your blessings and support to grow. Thanks

3 responses »

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s