I need help selecting a MySQL DateTime range in Java
I have the following code in my application
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 application 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 corresponding to your MySQL server version for the correct syntax to use around '00: 00: 00.0 AND 2014-08-20 00: 00: 00.0 ORDER BY UserId, Occurred 'on line 1
I'm sure there is a simple and sane solution to this, but I haven't been able to find one.
I tried looking in the MySQL manual for a solution or some other format.
I tried running my timestamps via the TIMESTAMP () functino and DATE () function in SQL, neither of which helped.
I pulled fully formed SQL from Java code and ran it in MySQL Workbench with no problem, never mind. So now I turn to specialists for help.
source to share
Dates in SQL must be enclosed in single quotes such as strings. When you use a prepared statemtent why don't you 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 reverse order. You must put "today" first, then lastDate. Although I don't know your limits ...
source to share