In OBIEE, we cannot perform an arithmetic operation on a DATE or TIMESTAMP. These are considered Non-numeric types.

To calculate difference between two dates, OBIEE provides a TIMESTAMPDIFF function. The syntax for using the function is:

TIMESTAMPDIFF(interval, first_date, second_date)

First_date and second_date have to be valid values in the TIMESTAMP type. Interval is a specified value.  Valid values for INTERVAL 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

If the dates are not in a valid TIMESTAMP format, use CAST function to convert values into TIMESTAMP type. See example below:

TIMESTAMPDIFF(SQL_TSI_DAY, CAST(VALUEOF("MY_DATE_COLUMN") as TIMESTAMP), CURRENT_DATE)

Date Difference Formula

In the example, we are subtracting MY_DATE_COLUMN date from CURRENT_DATE. Note how we apply CAST to MY_DATE_COLUMN. CURRENT_DATE holds the current date (it’s like SYSDATE in SQL) and has the TIMESTAMP type by default. The difference returned will be in number of days as we have used SQL_TSI_DAY interval.

You can also use TIMESTAMPADD function to add an interval to a date. Example:

TIMESTAMPADD(SQL_TSI_DAY, 7, TIMESTAMP '2011-11-01 12:00:00')