aspose file tools*
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 Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select Where Between syntax" Watch "Select Where Between syntax" New topic
Author

Select Where Between syntax

kelly devon
Ranch Hand

Joined: Apr 15, 2004
Posts: 75
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);
}


kel
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

You're welcome!
 
 
subject: Select Where Between syntax