File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes Select Where Between syntax Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select Where Between syntax" Watch "Select Where Between syntax" New topic

Select Where Between syntax

kelly devon
Ranch Hand

Joined: Apr 15, 2004
Posts: 75

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);
partdb = rs.getString("Particular");
amtdb = rs.getDouble("Amount");
datedb = rs.getString("Date");

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


Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

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
Ranch Hand

Joined: Apr 15, 2004
Posts: 75
Martin thanks for bringing this to light i have seen my mistake!!!
Thank you so much
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

You're welcome!
I agree. Here's the link:
subject: Select Where Between syntax
Similar Threads
DB problem
problem in extracting values from ResultSet
vector problem,how to retrieve data stored in vector and display in jsp pag