Searching between dates in SQL with JDBC?

by

Use quotes around your dates:

rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
            + "FROM sales, customers "
            + "WHERE sales.CardRecordID = customers.CardRecordID "
            + "AND customers.Name="Cash Sales" "
            + "AND sales.Date BETWEEN '" + sdate + "' AND '" + edate + "' "
            + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
            + ";");

Or it might be safer to use a prepared statement (if the dates come from untrusted inputs for example):

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date startDate = formatter.parse(sdate);
java.util.Date endDate = formatter.parse(edate);
PreparedStatement pstmt = connection.prepareStatement("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
            + "FROM sales, customers "
            + "WHERE sales.CardRecordID = customers.CardRecordID "
            + "AND customers.Name="Cash Sales" "
            + "AND sales.Date BETWEEN ? AND ? "
            + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC");
pstmt.setDate(1, new java.sql.Date(startDate.getTime()))
pstmt.setDate(2, new java.sql.Date(endDate.getTime()))

The between operator is inclusive, but if your database field is actually a timestamp, then a date with no time is assumed to be at time 00:00:00.000. So, in such a case, for your dates to be inclusive, you can add one day to your end date. Technically it will also include the first instant of the next day (00:00:00.000 hour), but depending on your application it may be enough.

Otherwise you could use >= on “start date” and < on “end date plus one day”:

"sales.Date >= '" + sdate + "' AND sales.Date < '" + edatePlusOne + "' "

More Related Contents:

  1. How to get the insert ID in JDBC?
  2. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  3. java.util.Date vs java.sql.Date
  4. Using setDate in PreparedStatement
  5. How do I get the size of a java.sql.ResultSet?
  6. How can I get the SQL of a PreparedStatement?
  7. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  8. Variable column names using prepared statements
  9. MySQL and JDBC with rewriteBatchedStatements=true
  10. right syntax to use near ‘?’
  11. Running a .sql script using MySQL with JDBC
  12. JDBC Pagination
  13. Getting output from dbms_output.get_lines using JDBC
  14. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  15. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  16. Java – Can’t use ResultSet after connection close
  17. Timezones in SQL DATE vs java.sql.Date
  18. Queries returning multiple result sets
  19. SQL prepared statement how to select via multiple possible menu selections?
  20. Class.forName(JDBC_DRIVER) no longer needed?
  21. dll missing in JDBC
  22. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  23. Java – Storing SQL statements in an external file [closed]
  24. Inserting null to an Integer column using JDBC
  25. How to use prepared statement for select query in Java?
  26. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  27. MS SQL Exception: Incorrect syntax near ‘@P0’
  28. Java JDBC connection status
  29. MyBatis executing multiple sql statements in one go, is that possible?
  30. Database independence through JDBC in java

Leave a Comment