Get previous months first and last date

Tags: , , , ,



I need to query last month data from a database. So I need the first and last date of previous month to be able to make the “between” SQL query.

First date starts always from 1 of course, but hard coding it looks bad. Also hard coding the date format “dd-MM-yyyy” seems like a bad practise. Is there a simpler way to do this? Right now I have something amateurish but working, like this:

        YearMonth thisMonth    = YearMonth.now();
        YearMonth lastMonth    = thisMonth.minusMonths(1);
        
        String dd = Integer.toString(lastMonth.lengthOfMonth());
        String mm = Integer.toString(lastMonth.getMonthValue());
        String yyyy = Integer.toString(lastMonth.getYear());
        
        String startDate = "01-" + mm + "-" + yyyy;
        String endDate = dd + "-" + mm + "-" + yyyy;

        System.out.println("startDate: " + startDate);
        System.out.println("endDate: " + endDate);

Answer

So I need the first and last date of previous month to be able to make the “between” SQL query.

You can do that computation directly in your database. In Oracle, you can get the first and last day of the previous month as dates like so:

where mydate between add_months(trunc(sysdate, 'month'), -1)
                 and last_day(add_months(trunc(sysdate, 'month'), -1))

In general, however, you don’t really need between and the last day. You can use half-open intervals – which is also safer, as it takes in account dates that belong to the last day of the month:

where mydate >= add_months(trunc(sysdate, 'month'), -1)
  and mydate <  trunc(sysdate, 'month')


Source: stackoverflow