Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

Number of Days in Month

Accepted answer
61
Views
1
Comments
Vismay Barot
Vismay Barot Rank 5 - Community Champion
in Oracle Analytics Forum

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

  • Gianni Ceresa
    Gianni Ceresa Mod
    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.