Number of Days in Month

Question

Greetings Community, is there any workaround possible that returns "Number of Days" in a Month. For ex, March = 31, April = 30 and so on and so forth.

I've tried many combinations from here:

but nothing seems to work.

Gianni Ceresa · Accepted Answer

Hi,

You need to start from a date, being a day in the month and year you need to know the number of days (because February has different days depending on the year).

Then, what you can do is to add 1 month to that date and remove the number of day of the month of your date and get the number of day for the date you got (it was the end of the month).

Here an example using CURRENT_DATE:

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

You can easily apply this formula to any date you have.

Just keep in mind that it would faster and "safer" (every user will always get the right number) if you add that information to your time dimension as an attribute.