Solution to find the Number of Days in a Month, without using Date Field in SAP HANA Modeler by using HANA Calculation Views

Introduction:

In this blog, I am going to explain how we achieved calculating the number of days in a month, without using the date field in the HANA Calculation view, by using a simple formula. This was a requirement from one of our customers and we had achieved it only by using the month field.

Main Problem:

In HANA Modeler, we have the following formula to find the number of days in a month or last day in a month, if the date field is available.

lastday(“date_field”)

But in case if the date field is not available, what could be the solution? This blog shows the solution here with a simple formula.

Solution:

To achieve the solution, the below steps need to be followed:

1. Convert the Year field to an integer, by using the below formula in a Calculated Column:

int(“year”)

2. In this step, we can see how the number of days in February can be derived for both Leap and Non-Leap Years. I have defined two formulae here as follows in the Calculated Column:

Formula 1:

if(((“Year_int” %  100)=0 AND (“Year_int” %  400)=0) OR (“Year_int” % 4 = 0 ),’29’,’28’)

Refer to the screenshot below:

Formula 2: if(“Year_int” % 100 = 0,if(“Year_int” %  400=0,29,28),if(“Year_int” %  4=0,29,28))

Refer to the screenshot below:

3. And here goes the final step. Use the below formula in the Calculated Column, as shown in the screenshot:

Formula: if(“month”=’01’or “month”=’03’or “month”=’05’or “month”=’07’or “month”=’08’or “month”=’10’or “month”=’12’,31,(if(“month”=’02’,”Leap_Feb”,30)))

In the above formula, the month field should contain values from ‘01’ till ‘12’.

The values that appear in the Data Preview is shown below against few example periods:

Conclusion:

So, we have got the number of days in a month without using the date field and I hope this blog should have clearly made you understand the steps and formulae involved to achieve this. For further information on why I used the formulae in this blog, kindly check the link in the Reference section. Feel free to post your comments if you have any other ideas to achieve this.

Reference:

• https://www.wikihow.com/Calculate-Leap-Years#:~:text=Check%20if%20the%20number%20is,it%20is%20a%20leap%20year.&text=For%20example%2C%201900%20is%20evenly,you%20a%20result%20of%204.75.