Useful TIMESTAMP Codes in OBIEE11g

Below Timestamp related codes are very very useful in Obiee. These codes are to be placed in the Column Formula (fx tab) or Can be used in Filter conditions in Criteria tab.

Current Date:

TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE)

Previous Date:


TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)

Next Date:

TIMESTAMPADD(SQL_TSI_DAY, 1, CURRENT_DATE)

Current Month Number:

MONTH(CURRENT_DATE)

* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Quarter Number:

QUARTER_OF_YEAR(CURRENT_DATE)


* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Year Number:

YEAR(CURRENT_DATE)


* Shows the results in Number format like 2012,2013,2014 etc. Shows the month number.

Current Month Name:

MONTHNAME(CURRENT_DATE)

 * Shows the results in character format like Jan,Feb,Mar etc. Shows the month number.

Last Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Next Month Start Date:

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Year Start Date:

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

 Last Year Start Date:

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Current Month End Date:

 TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Current Year End Date:

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Year End Date:

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,CURRENT_DATE))