Friday, January 18, 2013

[OBIEE11g] - How to get Month Start Date and Month End Date


Month Start Date:
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(MIN(“Dim – Date”.”Date Column “))+1, MIN(“Dim – Date”.”Date Column”))
Here DAYOFMONTH returns an integer in the range of 1 to 31. This integer represent the count of days since beginning of the month.
Month End Date:
TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(MAX(“Dim – Date”.”Date Column”)) * -1 ,TIMESTAMPADD(SQL_TSI_MONTH, 1, MAX(“Dim – Date”.” Date Column”)))
From  right to left the first TIMESTAMPADD returns next month.The second TIMESTAMPADD returns the date from current month.