This is a part of my code
java.sql.Date d1=java.sql.Date.valueOf("2011-03-02");
java.sql.Date d2=java.sql.Date.valueOf("2011-03-10");
java.sql.Date systemDate=java.sql.Date.valueOf("2011-03-04");
String sql="select id from period where '"+systemDate+"' between '"+d1+"' and '"+d2+"'";
This is my code. I want to get the id which falls between these dates. But i am not getting the desired result. I am getting back all the id present in the table.
Do you have good results when you use other SQL tools (like pgadmin for PostgreSQL or SQL Developer for Oracle)?
Is so then try to use PreparedStatement. In your case this will look like:
PreparedStatement pstmt = con.prepareStatement("select id from period where ? between ? and ?");
pstmt.setDate(1, systemDate);
pstmt.setDate(2, d1);
pstmt.setDate(3, d2);
You're not using a field from your table to compare with your date range.
What you're doing is checking if 04-03-2011 is between 02-03-2011 and 10-03-2011, which is true for every record.
What you want to do is something like this (assuming start_date is a column in your table):
String sql="select id from period where start_date between '"+d1+"' and '"+d2+"'";
(Also, I agree with the answer above that prepared statements are a better way to construct queries.)
