Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Number of Days in Month

Accepted answer
43
Views
1
Comments

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:

image.png

but nothing seems to work.

Best 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.