CODEWITHSUNDEEP
javamysqlsql
Toms
Toms

Reputation: 239

Whats the sql query for retrieving data from database between two dates, if the date values are stored on a variable?

I have been using MS access database, and i used this code to fetch records from the database on which it was working perfect. But now, as i changed my database to mysql, this piece of code is not fetching records from database. The database connections are working. The dates entered in the textbox are stored to a variable and i want to use that variables to fetch records from the database. Thanks for the help in advance.

        java.util.Date StartDate=(java.util.Date)textField1.getValue();
        java.util.Date EndDate=(java.util.Date)textField2.getValue();

        java.sql.Date startDate = new java.sql.Date(StartDate.getTime());
        java.sql.Date endDate = new java.sql.Date(EndDate.getTime());


        PreparedStatement ps = conn.prepareStatement("select * from table_nm where 'Date1 BETWEEN #startDate# AND #endDate#' ");
        ResultSet rs = ps.executeQuery();

        while(i8rs.next())
        {
            Date i8Date = rs.getDate("Date1");
            String i8ItemName = rs.getString("Item_Name");
            int i8Quantity = rs.getInt("Quantity");
            Double i8Rate = rs.getDouble("Rate");
            Double i8total = rs.getDouble("Total"); 


            Object[] row = new Object[5];
            row[0] = i8Date;
            row[1] = i8ItemName;
            row[2] = i8Quantity;
            row[3] = i8Rate;
            row[4] = i8total;
            tableModel.addRow(row);         

        }

Upvotes: 1

Views: 1633

Answers (2)

iamsuman
iamsuman

Reputation: 1413

Just use this 

PreparedStatement ps =  conn.prepareStatement("select * from table_nm 
                     where Date1 BETWEEN ? AND ? ");


 ps.setDate( 1, startDate );
ps.setDate( 2, endDate );
 ResultSet rs = ps.executeQuery();
while(rs.next())
    {
  // your table values

Upvotes: 4

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Assuming that Date1 is the column name in your table.

Change: 

PreparedStatement ps = 
  conn.prepareStatement("select * from table_nm 
                         where 'Date1 BETWEEN #startDate# AND #endDate#' ");

To: 

PreparedStatement ps = 
  conn.prepareStatement("select * from table_nm 
                         where Date1 BETWEEN ? AND ? ");

ps.setDate( 1, startDate );
ps.setDate( 2, endDate );

Refer To: Java: Using Prepared Statements

Upvotes: 5

Related Questions