Why query is not returning value

Question

Why this query is not returning any value. There are values in my database with in that range

value input are

Date startDate = (Date) data.get("startDate");//2014-04-01 
Date endDate = (Date) data.get("endDate");//2014-04-30
int pagesize=10;

sql query is

String query = "select * from bill_details "
 + " where status=? "
 + " AND date(add_date) BETWEEN " + startDate + " AND " + endDate
 + " order by add_date desc limit " + pagesize;
ps = con.prepareStatement(query);
ps.setString(1, dataStatus);
System.out.println("Before execution");
res = ps.executeQuery();

• Hamlet Hakobyan: What is this where status=??
• xrcwrn: @HamletHakobyan Please see updated qs status='active'
• NoGall: You're leaving out single quotes around your date strings. At least without more info.
• Hamlet Hakobyan: Use parameters instead of string concatenation.
• GarethD: BEWARE OF SQL INJECTION . Also BETWEEN is rarely the correct operator for dates - What do BETWEEN and the devil have in common?, or Bad habits to kick : mis-handling date / range queries

Answer 1

Try this:

Date startDate = (Date) data.get("startDate");//2014-04-01 
Date endDate = (Date) data.get("endDate");//2014-04-30
int pagesize=10;
String query = "select * from bill_details "
 + " where status=? "
 + " AND date(add_date) BETWEEN ? AND ?"
 + " order by add_date desc limit ?";
 ps = con.prepareStatement(query);
 ps.setString(1, dataStatus);
 ps.setDate(2, startDate);
 ps.setDate(3, endDate);
 ps.setInt(4, pagesize);
 System.out.println("Before execution");
 res = ps.executeQuery();

• jmail: this right one, try this code, your not set the value from the database,..this problem of your code

Answer 2

You're trying to concatenate a Date object to a String. Even if that works, then the date isn't surrounded with quotes in the query.

Use SimpleDateFormat to format the Date to a String.

Try this:

DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
String query = "select * from bill_details "
 + " where status='active' "
 + " AND date(add_date) BETWEEN date('" + df.format(startDate) + "')"
 + " AND date('" + df.format(endDate) + "') " +
 + " order by add_date desc limit " + pagesize;

• xrcwrn: So date is treated as string