Month Start Date in OBIEE 11g

We have the common need for Year Start Date, Month Start Date  and Week Start Date when we are working in OBIEE 11g. This is a basic need when we are working with measures like forecasting and lags.

The below are the codes which we can directly use to get the different dates from the date available.

For instance i'm taking the system date ........CURRENT_DATE


Year Start Date :

       TIMESTAMPADD(SQL_TSI_DAY,-       (DAYOFYEAR(CURRENT_DATE)-1),CURRENT_DATE)



Month Start Date :

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



Week Start Date :

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