How to calculate Last day of the month for the last 6 months

Deb - Oracle-Oracle

Hello,

I wanted to create a calculation to get the last day of the month for the last 6 months, I would appreciate a suggestion how I can do that? i.e. what function to use?, does OAC have a feature I can turn on or off for this?

Gianni Ceresa

Yes, you can use a calculation where you enter your formula.

The functions you will use are just 2: TIMESTAMPADD to add or substract days and months, and DAYOFMONTH to have the number of the day of the month.

The logic is just what I said above.

For a given date, you will subtract (1 - number of the day of the month for that date) days from your date. This give you the 1st day of the month for that given date.

Then you add 1 month to it, to get the 1st day of the month of the following month of the given date.

And finally you subtract 1 day to get the last day of the month of the given date.

But it's up to you to have a column giving you 6 dates for your 6 months. Otherwise you need to create 6 calculation using CURRENT_DATE and then subtracting 1, 2, … 6 months. And you will need to use those 6 calculation (you can also make them parameters or whatever you want, but it will be 6: because the tool doesn't generate rows of data out of nowhere).

PS: I could have posted the formula, but I don’t do it on purpose because it’s more important to understand the logic and functions than just copy a piece of code randomly from a forum.

And the logic I posted can be optimized with a timestampadd less easily, but again its easier to take a longer path at first to get the concept… The shortest form has been posted below, despite being hardcoded for a single date.

RichardChan

yes unfortunately there is no LAST_DAY_OF_MONTH function, you have to go with -1 day from the following month to get the date for the preceeding month

Chere-Oracle

Example: DAYOFMONTH( TIMESTAMPADD( SQL_TSI_MONTH, -5, TIMESTAMPADD( SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE), CURRENT_DATE ) ) )

The inner TIMESTAMPADD() is subtracting the DayOfMonth result from the current date, getting the final date of last month. So the outer TIMESTAMPADD() is subtracting 5 months from the last date of last month.

Gianni Ceresa

Hi,

In what place of OAC are you trying to do that?

Because the last day of a month is something "dynamic" (28, 29, 30 or 31), you can generally easily get it by combining a number of simple functions available in the tool (assuming "classic", DV workspace or RPD): subtract the day of month from a date, add 1 month, substract 1 day. Because nothing "generate" rows of data out of nowhere, this works by applying that formula to any date (it's up to you to get 6 records, one for each month).

But still, at the end of the day, the ideal way to handle this is by having a clean, well build, time dimension with all the required attributes available all the time (first day or months, last day of month, ago dates etc.).

[Deleted User]

Amen to that @Gianni Ceresa. Properly modeled time dimension :)

Deb - Oracle-Oracle

@Gianni Ceresa - I am trying to do it on the workbook side. does this mean I have to use a calc for it? if so which functions would be helpful.

kalikhan

Some examples

• First Day of the CURRENT Month:

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 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))

Gianni Ceresa

@kalikhan , are you trying some GenAI service and randomly posting it?

How about first reading the generate answer and see if it does make sense? If you really believe you need 4 calls to TIMESTAMPADD to get the first day of current month, you better think at it twice.

Please don't post GenAI content: if people would like ChatGPT or other generated randomness, they would ask there and not in a forum.

[Deleted User]

One point to remember: Whatever logic or code you use - it will get executed row-by-row. So some examples in this thread may work nicely in theory and in practice for 20 rows but probably aren't really a viable choice to run against data sets that source themselves from multi-billion row fact tables…

Edit: And always test all formulas posted in here in detail. As Gianni said it's about the concept and not all code posted here will yield the results you expect ;)

Gianni Ceresa

By the way, the formulas are wrong… Or maybe somewhere in a virtual world the first day of current month is November 30.