22 January 2013

To Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.