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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s