CODEWITHSUNDEEP
sqlsqlitedatejdbc
joppiealiw
joppiealiw

Reputation: 43

SQL Statement - Java

I've been trying to come up with a couple of SQL statements on how to get this right but no luck. I've tried between, >= and =<. Basically, the SQL statement that I've used is working but to an extent only.

My code works like this: the user will choose a date range (from date and to date) and the program will retrieve and show the data it has within those range. Like I said, it works but it also shows the days from the other months when what I want to show is just those particular days that the user picked. eg. from July 1, 2016 to July 5, 2016. What's happening is any month of the year that has those dates will show as well which makes that particular method a bit useless.

Any help or any explanation why is this so would be appreciated.

Below is my code:

stringFromDate = sdf.format(fromDate.getDate());

stringToDate = sdf.format(toDate.getDate());

String query = "Select * from tblSavings where date between '" + stringFromDate+ "' and '" + stringToDate+"'";

try{ 

    pstmt = conn.prepareStatement(query);

    rs = pstmt.executeQuery();

    tblList.setModel(DbUtils.resultSetToTableModel(rs));

Upvotes: 1

Views: 61

Answers (2)

Mureinik
Mureinik

Reputation: 311228

Seems like you're trying to re-invent the wheel here. PreparedStatements were created exactly for this usecase - setting variable values in a predefined structure. In your case, with the setDate method. From the context I'm guessing your fromDate and toDate variables are java.util.Date instances, so you'll have to convert them to java.sql.Dates

java.sql.Date fromDateToBind = new java.sql.Date(fromDate);
java.sql.Date toDateToBind = new java.sql.Date(toDate);

String query = "Select * from tblSavings where date between ? and ?";

try{ 
    pstmt = conn.prepareStatement(query);
    pstmt.setDate(1, fromDateToBind);
    pstmt.setDate(2, toDateToBind);
    rs = pstmt.executeQuery();

    // use the results...
} // etc...

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You can use SimpleDateFormat to get a string which is in proper format to be used as a date in SQLite, the proper format being yyyy-MM-dd:

String pattern = "yyyy-MM-dd";
SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
String stringFromDate = dateFormat.format(fromDate.getDate());

Upvotes: 1

Related Questions