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.

1 comment:

  1. Hi,
    I've one requirement,requirement is I've Header and Projection values.
    Header logic is (Q1+Q2),YTD logic is (Q1+Q2+Q3). Actually currently running quarter is Q3 so that's why above logic is matching for current quarter,but my end is i did static report,they want dynamically.If Q4 comes Header logic is (Q1+Q2+Q3),YTD logic is (Q1+Q2+Q3+Q4).They would like to see YTD and Header wise achievement.
    Please help me on this.
    Thanks in advance.

    Regards,
    Ilayaj Shaik

    ReplyDelete