Select Where Between syntax

[kelly devon]

Goodday,

please i am getting a jtable to display values between a date period. when this part of syntax executes, it displays all the values in the database without displaying the range i asked it to.

please help.
thank you in advance

rs=stat.executeQuery("SELECT * FROM Incomedb WHERE Date BETWEEN'"+getfrmdate+"'AND'"+gettodate+"'");
//System.out.println(getfrmdate + ""+""+ gettodate);
while(rs.next()){
partdb = rs.getString("Particular");
amtdb = rs.getDouble("Amount");
datedb = rs.getString("Date");

gettotal = amtdb+gettotal;
// System.out.println(partdb);
Vector tmp = new Vector();
tmp.addElement(partdb);
tmp.addElement(amtdb);
tmp.addElement(datedb);

data1.addElement(tmp);
}

[Martin Vashko]

The syntax is probably right. I'd suggest printing out the full SQL query text and running it in an SQL client to see how it works.

One possible caveat is that your code probably relies on implicit conversion of the strings you're stuffing into the SQL query to dates. This is bad, you should avoid implicit conversions. It is possible that the database expects a different date format from what you've used (eg. DD/MM/YYYY instead of MM/DD/YYYY), which could lead into providing an unexpected output or an error, depending on the dates. All this assumes that the Date column is actually stored as a date in the database, not as a text. (Storing dates as texts in the database results in different class of problems and should not be really done either.)

The correct way is to use a PreparedStatement and set the dates directly, not converting them to text.

Also, you should not use SELECT *, use column lists instead.

[kelly devon]

Martin thanks for bringing this to light i have seen my mistake!!!
Thank you so much

[Martin Vashko]

You're welcome!