Friday, 27 January 2012

TimestampDiff and TimestampADD( Difference of two dates) in OBIEE

How to get a difference between two dates (in terms ) of days,weeks,months what every it may be

The below formula gives you no.of days between day date and current_Date
Days difference: 
TIMESTAMPDIFF(SQL_TSI_DAY, Time."Day Date",CURRENT_DATE)
The below formula gives you no.of months day date and current_Date
Months difference:
TIMESTAMPDIFF(SQL_TSI_MONTH, Time."Day Date",CURRENT_DATE)

The below formula adds months to day date column
Toadd 12 months to a date column:
TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Day Date")


similarly we can write the formulas using different intervals based on the date format in the column
Here are the intervals :
SQL_TSI_SECOND, 
SQL_TSI_MINUTE, 
SQL_TSI_HOUR, 
SQL_TSI_DAY, 
SQL_TSI_WEEK, 
SQL_TSI_MONTH, 
SQL_TSI_QUARTER, 
SQL_TSI_YEAR.

No comments:

Post a Comment