CODEWITHSUNDEEP
javamysql
Mahmoud Hassan Mohamed Hassan
Mahmoud Hassan Mohamed Hassan

Reputation: 1

how to load data from mysql using in between two dates

In the code below using java, I am trying to get data from mysql between two choosed dates and load it into table jTable1 but the problem is when I run the data loads correctly but not until the second date.

For example, if first date is 2020-1-1 and second date is 2020-1-31 the data loads from the day of 1 to the day of 30 not until the day of 31. The last day is missing! And if I choose dates from 2020-01-01 to 2020-02-01 it loads from 2020-01-01 to 2020-01-31.

java.sql.Timestamp timestamp = new java.sql.Timestamp(jDateChooser5.getDate().getTime());
java.sql.Timestamp timestamp2 = new java.sql.Timestamp(jDateChooser6.getDate().getTime());

String sql2 = "SELECT sum(`msareffishmarket`.`Amount` )  FROM `manfz`.`msareffishmarket`  \n"
                                    + "Where `msareffishmarket`.`place` = 'محل الأسماك'  And ( `msareffishmarket`.`MsarefDate` between '" + timestamp + "' And '" + timestamp2 + "' ) "
                                    + " group by  DATE(`msareffishmarket`.`MsarefDate`)";

stm2 = conn.prepareStatement(sql2);
rs2 = stm2.executeQuery(sql2);

//  JOptionPane.showMessageDialog(this, rs.getDouble(4)); 
jTable3.setModel(DbUtils.resultSetToTableModel(rs2));

Upvotes: 0

Views: 178

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522109

You are using your prepared statement incorrectly (though you are correct to think to use one). Consider this version of your code:

String sql = "SELECT MsarefDate, SUM(Amount) AS total FROM manfz.msareffishmarket ";
sql += "WHERE place = 'محل الأسماك' AND MsarefDate BETWEEN ? AND ? GROUP BY MsarefDate";
stm2 = conn.prepareStatement(sql);
stm2.setTimestamp(1, timestamp);
stm2.setTimestamp(2, timestamp2);
rs2 = stm2.executeQuery();  // do not pass the query string here

Edit:

Per the correct observations of @mangusta (see below), you should be using a date/timestamp range to search for your records. Assuming you wanted all records from January 2020, you should be using:

WHERE MsarefDate >= '2020-01-01' AND MsarefDate < '2020-02-01'

Upvotes: 1

Related Questions