OBIEE doesn't have a built in function for the first and or last of a month. This can be easily achieved by the follwing functions:
First of month:
TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH(CURRENT_DATE) * -1) + 1, CURRENT_DATE)
Last of month:
TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , TIMESTAMPADD(SQL_TSI_MONTH, 1, CURRENT_DATE))
Till Next Time
2 comments:
John, your SQL for the Last Day of the Month needs to be modified. If the current month is July, than the first part of the SQL will give you June 30th. Adding the one month to that will give you July 30th which is not the last day of the month. This will work:
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))))
I have some useful DB scripts in my collection above a look here -- uses full DB scriptsder
Post a Comment