OBIEE Date Expression Reference

Often en course of your development, you need to use a lot of customized date functionality in OBIEE.

Here is a one stop shop for all the First and Last days of the Current/ Previous/ Next (Year/ Quarter/ Month/Week)

PREVIOUS - (YEAR/ QUARTER/  MONTH/WEEK)

First Day of the Previous Year

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

First Day of Previous Quarter

TIMESTAMPADD( SQL_TSI_QUARTER , -1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Previous Month

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

First Day of the Previous Week

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)-6),CURRENT_DATE)

Last Day of Previous Year

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

Last Day of Previous Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last Day of the Previous Month

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

Last Day of the Previous Week

TIMESTAMPADD(SQL_TSI_DAY, (DAYOFWEEK(CURRENT_DATE) *-1),CURRENT_DATE)

CURRENT - (YEAR/ QUARTER/  MONTH/WEEK)

First Day of the Current Year

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

First Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 
 
First Day of the Current Week

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+1),CURRENT_DATE)

Last Day of Current Year

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 Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of Current Month

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

Last Day of Current Week

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+7),CURRENT_DATE)

NEXT - (YEAR/ QUARTER/  MONTH/WEEK)

First Day of the Next Year

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

First Day of Next Quarter

TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Next Month

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

First Day of the Next Week

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+8),CURRENT_DATE)

Last Day of the Next Year

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

Last Day of Next Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 2, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Day of the Next Month

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

Last Day of the Next Week

 TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+14),CURRENT_DATE)

Cheers!

-Gautam