SQL Select where timestamp column is between two timestamps

Question

I'm trying to get a SQL statement to select rows where a timestamp is between two given timestamps. here is what i tried. (this is on a Java servlet, and i java timestamp objects

SELECT * FROM dates WHERE dateAvailable >= " + from.toString() + " AND dateAvailable <= " + to.toString();

where from and to are timestamp objects this is giving me a syntax error when trying to prepare the statement, please help

**Side note**: This way of making a query is _extremely vulnerable_ to SQL-Injections. Use [Prepared Statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to add values to a query instead of just concatenating them in a string. — BackSlash, Oct 10 '13 at 18:16

score 4 · Accepted Answer · edited May 23 '17 at 12:06

4

JDBC has built-in types for this. Use

PreparedStatement ps = con.prepareStatement(
              "SELECT * FROM dates WHERE dateAvailable BETWEEN ? AND ?"); 
ps.setTimestamp(1, from);
ps.setTimestamp(2, to);

You specify two ? placeholder. You can set these to your TimeStamp elements.

Because it might not be immediately obvious, one purpose of using PreparedStatement is it helps prevent SQL injection. Read the answers here for more details.

The reason yours wasn't working is that Timestamp#toString() has a specific format that does not work with SQL.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 10 '13 at 18:15

Sotirios Delimanolis

274,122
60
696
724

+1, but I would also say _why_ it is better to use this implementation rather than building the query directly with string concatenation :) – BackSlash Oct 10 '13 at 18:17
@BackSlash Sure, added link to other question/answers. – Sotirios Delimanolis Oct 10 '13 at 18:22

score -1 · Answer 2 · answered Oct 10 '13 at 18:16

-1

You gotta use DATE functions otherwise you'll get strange side effects anyhow.

answered Oct 10 '13 at 18:16

brez

44
3

SQL Select where timestamp column is between two timestamps

2 Answers2