In OBIEE, You can use TIMESTAMPADD function to add an interval
to a date.
First Day of the CURRENT Month:
TIMESTAMPADD(SQL_TSI_DAY,
-1,TIMESTAMPADD(SQL_TSI_MONTH,1,TIMESTAMPADD(SQL_TSI_DAY,1,TIMESTAMPADD(SQL_TSI_DAY,
DAYOFMONTH(CURRENT_DATE)*-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)))
· First Day of
the Previous Month
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY ,
DAYOFMONTH( 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))
· First Day of
the Next Month
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY ,
DAYOFMONTH( 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)))
· First Day of
the Current Year
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM
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)))
· 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))
· 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))
· 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))
· 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)))
· First Day of
Current Quarter
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( 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)))