During a recent project I had several requirements that involved converting dates to the first or last day of the current month, past month, future month etc. Thanks to that project I’ve become proficient at Date manipulations and thought I would share some of the expressions I had to use on the project and how they worked. For people who have not worked a lot with OBIEE Date manipulation this may come in handy since OBIEE does not offer built in functions like Oracle which has the LAST_DAY function to return the last day of a given month.
Any date manipulation within OBIEE will utilize the TIMESTAMPADD function. For the examples shown in the table below I used CURRENT_DATE as the starting point for all dates. You can replace CURRENT_DATE with whatever valid Date type you need as a starting point or can CAST a CHAR value to a Date type if needed.
The table below describes what is being calculated, the expression to perform the calculation, and then an explanation that explains how the expression arrives at the desired date value.
Date Calculation | OBIEE Expression | Explanation |
First Day of the Previous Year | TIMESTAMPADD( SQL_TSI_YEAR , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year. |
First Day of the Current Year | TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE) | This calculation returns the first day of the year by deducting one less than the total number of days in the year. |
First Day of the Next Year | TIMESTAMPADD( SQL_TSI_YEAR , 1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year. |
First Day of the Previous Month | TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month. |
First Day of the Current Month | TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) | This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month. |
First Day of the Next Month | TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month. |
First Day of Current Quarter | TIMESTAMPADD( SQL_TSI_DAY ,DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) | This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter. |
Last Day of the Previous Month | TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month. |
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))) | From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month. |
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))) | From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month. |
Last Day of Previous Year | TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)) | From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year. |
Last Day of Current Year | TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE))) | From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year. |
Last Day of the Next Year | TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE))) | From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year. |
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))) | Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter. |
Number of days between First Day of Year and Last Day of Current Month | TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_MONTH , 1,TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) | For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month. |
Posts
