How to filter data by date (obtained from a JCalendar) using MySQL

Question

I'm having a hard time trying to filter data by date

MySQL Query

public DefaultTableModel getData1(Date start,Date end){
.......
 String sql = "SELECT * FROM torch.new_table WHERE pro_date between '"+start+"' and '"+end+"'";
.....
}

Code for get date from a JCalendar

DefaultTableModel dm = new Products().getData1(sDate.getDate(), eDate.getDate());

Complete code is Here: http://postimg.org/image/4zycbtj4n/

Use a PreparedStatement http://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement — RubioRic, Apr 24 '16 at 09:08
@MuhammadKashifSaeed I've answered your question. Try it and please, mark it as accepted if it helps you. Also, please, edit your question and include your code properly instead of linking images. — RubioRic, Apr 24 '16 at 11:00
Sorry i'm beginner so i can't understand what u say..... RubioRic just simply give me code with example. — Muhammad Kashif Saeed, Apr 24 '16 at 16:31

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Use the wildcard '?' when you are defining the query.

Replace each wildcard using setDate and a new java.sql.Date.

...

String sql = "SELECT * FROM torch.new_table WHERE pro_date between ? and ?";

PreparedStatement ps = con.prepareStatement(sql);

ps.setDate(1, new java.sql.Date(start.getTime());
ps.setDate(2, new java.sql.Date(end.getTime());

ResultSet rs = ps.executeQuery();
...

If you obtain a String from a JDateChooser, you can use

ps.setDate(1, java.sql.Date.valueOf(start));
ps.setDate(2, java.sql.Date.valueOf(end));

More possibilities here Using setDate in PreparedStatement

edited May 23 '17 at 11:52

Community

1
1

answered Apr 24 '16 at 10:58

RubioRic

2,442
4
28
35

in jdateChooser can't convert string to long plz help – Muhammad Kashif Saeed Apr 24 '16 at 16:35
@MuhammadKashifSaeed I think that you are using there java.util.Date instead of java.sql.Date. Also, I got a typo. Try removing "new" [Updated my answer again] – RubioRic Apr 24 '16 at 19:13

score 0 · Answer 2 · answered Apr 26 '16 at 03:57

Part of code to filter data by date (Obtained from a jCalender) using MySQL

Thanks for help @RubioRic

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

//dateT and dateE type jDatechooser

    String get = sdf.format(dateT.getDate());

    String get1 = sdf.format(dateE.getDate());

// Query to select get data from database

    String sql = "SELECT * FROM date WHERE DATE(Date) >=   ?  && DATE(Date)  <= ?";

    try{

        Connection conn = dataB.mySqlCon();

        PreparedStatement st = conn.prepareStatement(sql);

       st.setString(1, get);
       st.setString(2, get1);

        ResultSet rs = st.executeQuery();

How to filter data by date (obtained from a JCalendar) using MySQL

2 Answers2