Get previous months first and last date

user_10030611 0
Viewed 352

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);
where-clause datetime oracle sql java
4 Answers

The folllowing adjusters are at your disposal:

  1. TemporalAdjusters.firstDayOfMonth()

  2. TemporalAdjusters.lastDayOfMonth()

Demo:

import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;

class Main {
    public static void main(String[] args) {
        // Today's date
        LocalDate today = LocalDate.now();

        LocalDate oneMonthAgo = today.minusMonths(1);

        LocalDate firstDateOfLastMonth = oneMonthAgo.with(TemporalAdjusters.firstDayOfMonth());
        LocalDate lastDateOfLastMonth = oneMonthAgo.with(TemporalAdjusters.lastDayOfMonth());

        System.out.println(firstDateOfLastMonth);
        System.out.println(lastDateOfLastMonth);
    }
}

Output:

2020-11-01
2020-11-30

Learn more about the modern date-time API at Trail: Date Time.

How to use Java 8 Date and Time API (JSR-310) with JDBC 4.2:

String query = "SELECT column_name(s)
                FROM table_name
                WHERE column_name BETWEEN ? AND ?";

PreparedStatement st = conn.prepareStatement(query);
st.setObject(1, firstDateOfLastMonth);
st.setObject(2, lastDateOfLastMonth);
st.executeQuery();

//...Process ResultSet 

st.close();
user_108195730

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')
user_106767160

Transfer LocalDate objects to your SQL query

Don’t send the first and last date as strings to your database query. Since JDBC 4.2 transfer LocalDate objects. There are a couple of ways to do the calculations in Java. My preferred way would use YearMonth as you do in the question.

    YearMonth thisMonth    = YearMonth.now(ZoneId.of("Atlantic/Reykjavik"));
    YearMonth lastMonth    = thisMonth.minusMonths(1);
    
    LocalDate startDate = lastMonth.atDay(1);
    LocalDate endDate = lastMonth.atEndOfMonth();
    
    System.out.println("startDate: " + startDate);
    System.out.println("endDate: " + endDate);

Output when running today:

startDate: 2020-11-01
endDate: 2020-11-30

To pass to JDBC:

    String query = "select * from your_table where your_date_col between ? and ?;";
    PreparedStatement statement = yourDatabaseConnection.prepareStatement(query);
    statement.setObject(1, startDate);
    statement.setObject(2, endDate);
user_57728820

You can use TemporalAdjusters.firstDayOfMonth() and TemporalAdjusters.lastDayOfMonth()

Refer to document: https://docs.oracle.com/javase/8/docs/api/java/time/temporal/TemporalAdjusters.html#lastDayOfMonth--

LocalDate currentDate = LocalDate.now();
LocalDate currentDateLastMonth = LocalDate.of(
                currentDate.getYear(), 
                currentDate.getMonth().minus(1), 
                currentDate.getDayOfMonth());

LocalDate firstDayOfMonth = currentDateLastMonth.with(TemporalAdjusters.firstDayOfMonth());
LocalDate lastDayOfMonth = currentDateLastMonth.with(TemporalAdjusters.lastDayOfMonth());

System.out.println("first day of the month "+ firstDayOfMonth);
System.out.println("last day of the month "+ lastDayOfMonth);
user_132027760
Related