0

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?

Pang
  • 9,564
  • 146
  • 81
  • 122
Abhay Singh
  • 1
  • 1
  • 1
  • what kind of code have you tried? – Radim Bača Oct 12 '17 at 06:41
  • 2
    Possible duplicate of [Searching between dates in SQL with JDBC?](https://stackoverflow.com/questions/19583806/searching-between-dates-in-sql-with-jdbc) Or https://stackoverflow.com/questions/22309455/select-statement-between-two-java-sql-date – nos Oct 12 '17 at 06:43

1 Answers1

1

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'