I need help selecting within a MySQL DateTime range in Java

Question

I've got the following code in my app

String sql = "SELECT colA, colB, colC " + 
    "FROM " + tblName + " WHERE UserId = " + userId + 
    " AND InsertTimestamp BETWEEN " + lastDate + 
    " AND " + DataProcessor.TODAY + " ORDER BY UserId, Occurred";
try{
  if(null == conn)
    openDatabaseConnection();
  PreparedStatement stmt = conn.prepareStatement(sql);
  ResultSet rs = stmt.executeQuery();  <------- this is the line which throws the SQL exception
  retArray = this.getArrayListFromResultSet(rs);
}catch(SQLException sqle){
  JSONObject parms = new JSONObject();
  eh.processSQLException(methodName, sqle, sql, parms);
}

So when I run my app in the debugger, I get this exception message

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00.0 AND 2014-08-20 00:00:00.0 ORDER BY UserId, Occurred' at line 1

I'm reasonably certain that there's simple and reasonable solution to this, but I have not been able to find it.

I've tried looking in the MySQL manual for a solution or a different format.

I've tried running my timestamps through a TIMESTAMP() functino and a DATE() function in the SQL, neither of which helped.

I pulled the fully formed SQL out of the Java code and ran it in MySQL Workbench with no issues, what-so-ever. So now I'm looking to the experts for help.

Answer 1

Dates in SQL must be enclosed within single quotes like strings. As you're using a prepared statemtent, why you don't use '?' and stmt.setDate(...)?

String sql = "SELECT colA, colB, colC " + 
"FROM " + tblName + " WHERE UserId = ?" + 
" AND InsertTimestamp BETWEEN ?" + 
" AND ? ORDER BY UserId, Occurred";
try {
    if(null == conn) {
        openDatabaseConnection();
    }
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setInt(1, userId);
    stmt.setDate(2, lastDate);
    stmt.setDate(3, DataProcessor.TODAY);
    ResultSet rs = stmt.executeQuery();  
    retArray = this.getArrayListFromResultSet(rs);
} catch(SQLException sqle) {
    JSONObject parms = new JSONObject();
    eh.processSQLException(methodName, sqle, sql, parms);
}

Anyway, I think you are setting the dates in the opposite order. You should put first 'today' then lastDate. Although I don't know your constraints...