CODEWITHSUNDEEP
sql
Abhay Singh
Abhay Singh

Reputation: 1

How to use between in prepared query statement

For example, if we have to find some data from a table which lies between two specific date, that we write query like this:

Select * from TableName where Date lies between '01-01-2000' and '01-01-2002'

I just want to know how to use this query in PreparedQueryStatement?

Upvotes: 0

Views: 4381

Answers (1)

user330315
user330315

Reputation:

You need to use a string with placeholders and then pass instances of java.sql.Date to the PreparedStatement

String query = "Select * from TableName where some_date_column between ? and ?";
java.sql.Date dateOne = java.sql.Date.valueOf(LocalDate.of(2000, 1, 1));
java.sql.Date dateTwo = java.sql.Date.valueOf(LocalDate.of(2002, 1, 1));

PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setDate(1, dateOne);
pstmt.setDate(2, dateTwo);
ResultSet rs = pstmt.executeQuery();
... process the result ...

Never pass dates (or timestamps or numbers) as strings!

Note that the SQL BETWEEN operator includes both values. The expression

some_date_column BETWEEN date '2000-01-01' and date '2002-01-01'

is equivalent to:

    some_date_column >= date '2000-01-01' 
and some_date_column <= date '2002-01-01'

If you want to exclude rows from '2002-01-01' you need:

some_date_column BETWEEN date '2000-01-01' and date '2001-12-31'

Upvotes: 1

Related Questions