Lessons learnt from getting the last day of any month

First a little background

During the second year of my first position as a software developer there was a period during which I got in touch with SQL and JasperReports more than with Java. I had to work on JasperReports, but the data for the report was quite messy. In order to avoid handling this in the report, I prepared the data I needed in SQL views. However, some data processing was done in the reports nevertheless.

The task

One of the things I did in the report processing was to convert dates to the last day of the same month. The input was a something like 14.03.2021, so the expected result would be 31.03.2021

I think it was because due to some limitations in the reporting software (or my limited skills using it) that did not have some functionality like LAST_DAY. It all had to be done in JasperReport, there was no processing in Java or some Backend. Hard coding which month had how may days in the report would not work for February.

Besides, this cannot be the solution! I could not come up with a solution to this problem by myself. Searching online also did not help (having repeated some research for this blog post, only proves that I did not search properly back then).

Luckily, I was not alone: my senior colleague showed me a nice approach to find the last day of any month.

A good approach

While 31.04.2021 and 30.02.2021 are not valid dates, the first of any month always is valid. So instead of trying to find the last day of a month it is much more elegant to convert the input to the first day of the following month, before subtracting one day.

Obviously I cannot reproduce the exact code anymore. Moreover, my knowledge about JasperReports has faded. Assuming an INPUT of a date with the pattern of, e.g. 14.03.2021, the result was some monstrosity like the following:

// this is just pseudocode from the top of my head!
// do yourself a favour and do not copy and paste this
Date.add(new SimpleDateFormat("dd.MM.yyyy").format("01."+$F{INPUT}.getMonth()+1+"."+$F{INPUT}.getYear()),-1);

I remember having to solve everything with one-liners. Using multiple lines, readability becomes much better, but the code still is not very good:

// this still is just pseudocode from the top of my head!
// do yourself a favour and do not copy and paste this
DateFormat df = new SimpleDateFormat("dd.MM.yyyy");
String firstDayOfNextMonth = "01." + $F{INPUT}.getMonth()+1 + "." + $F{INPUT}.getYear();
Date.add(df.format(firstDayOfNextMonth), -1);

Some lessons learnt

In hindsight, I maybe should have prepared the date in the SQL view as well. I am quite sure this would have been possible. That most probably would have saved me a lot of sweat, but it also would have cost me this nice story.

In general, the correct tool should be used for the correct task. Know your tools: If there are tools at hand for certain tasks, use them. Lastly, know how to search for solutions properly—maybe I did not dig deep enough, but using Duckduckgo or Google now, returned sufficient results.