How to only get the day from the date stored in database in java?

Question

I can retrieve the complete date of all the records but I only want to retrieve the day of each record. For example I have a date 2017-01-20, but I only want to retrieve 20. How do I do that? Here is my code:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String date = sdf.format(calendar.getDate());

    String sql = "SELECT reserve_date FROM reservation";

    try
    {
    pst = conn.prepareStatement(sql);
    rs = pst.executeQuery();
    text.setText("");
    while(rs.next()){
        String str = rs.getString("reserve_date");
        text.append(str);
        text.append("\n");
    }
        pst.close();
        rs.close();
}
catch(Exception e)
{
    JOptionPane.showMessageDialog(null, e);
}
 finally{
        try{
            rs.close();
            pst.close();
        }catch(Exception e){JOptionPane.showMessageDialog(null, e);}
    }

Any reason why you are using the long outdated and notoriously trouble some `SimpleDateFormat` class and friends? [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/), is so much nicer to work with. Then it’s as easy as calling `LocalDate.getDayOfMonth()`. — Ole V.V., Dec 02 '17 at 06:36

score 3 · Accepted Answer · answered Dec 02 '17 at 04:20

3

You could use the MySQL function DAYOFMONTH like

String sql = "SELECT DAYOFMONTH(reserve_date) FROM reservation";

And then either

String str = rs.getString(1);

or

int dayOfMonth = rs.getInt(1);

answered Dec 02 '17 at 04:20

Elliott Frisch

198,278
20
158
249

Basil Bourque · Answer 2 · 2017-12-02T16:45:21.513

tl;dr

myResultSet.getObject( reserve_date , LocalDate.class )  // Retrieve `LocalDate` object from database. 
           .getDayOfMonth()                              // Extract an integer for day-of-month number.

Details

The Answer by Frisch is correct. Another route is through the java.time library.

Fetch from the database using objects, specifically a java.time.LocalDate object. The interrogate for the day-of-month.

LocalDate ld = myResultSet.getObject( reserve_date , LocalDate.class ) ;
int dayOfMonth = ld.getDayOfMonth() ;

How to only get the day from the date stored in database in java?

2 Answers2

tl;dr

Details