Skip to Content
Technical Articles
Author's profile photo Akash AJ

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Konrad Zaleski
      Konrad Zaleski

      Wouldn't be much simpler to generate the date out of concatenation of year,month and 01 as day and then using lastday function?

      lastday(date("YEAR" + "MONTH" + '01'))
      Author's profile photo Akash AJ
      Akash AJ
      Blog Post Author

      Dear Konrad,

      Thanks. That's really a good idea. But in my case, it didn't work out. I've tried the same before the current approach and I got an error as mentioned below:

      The%20Calculated%20Column%20that%20I%20have%20used

      The Calculated Column that I have used

       

      The%20Error%20That%20I%20got

      The Error That I got

       

      You can let me know where I would have gone wrong, if you can identify the issue.

      Anyway, many thanks for suggesting this.

       

      Regards,

      Akash.

      Author's profile photo Konrad Zaleski
      Konrad Zaleski

      I think this might be some issue with your data set. Make sure that in month column you have only values from 01 to 12. Error message indicates that there is some issue when trying to convert string to date,

      I checked this formula and it works as expected.

      Author's profile photo pavan sandra
      pavan sandra

      I have tried above two cases, but did not work.

      I have a data as shown in below image.

      I have created a calculated column with the expression as shown below.

      I am getting an error as shown in below screenshot.

      Can you please, let me know where I would have gone wrong, if you can identify the issue.

       

      Thanks in Advance.

      Author's profile photo Akash A J Anbazhagan K1541
      Akash A J Anbazhagan K1541

      Dear Pavan,

      Sorry, that I missed to notice your problem earlier. This seems to be a problem with the datatype of the columns you have used in the formula.

      Is this issue already already sorted out?

      Thanks,

      Akash AJ