Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Searching between dates in SQL with JDBC?

Tags:

java

sql

jdbc

I'm currently writing a Java Swing application that reads data in from a MYOB database file and displays certain information in a table. I've been able to successfully generate the required SQL statements but I'm having trouble adding the ability to search between dates (our database is pretty large so we are trying to limit results). An example of one of my queries is below (written in Java):

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

I have two dates (they are actually Strings in Java but are formatted as dd/MM/yyyy).

I have tried using another AND clause in my WHERE with a BETWEEN statement but I get the following error from JDBC [MYOB ODBC]Error getting the literal value of right operand.

Actual statement is below:

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"
                        + ";");

Is anyone able to help me work out correct syntax for this? It's the last piece of functionality missing.

EDIT: Current value of sdate and edate respectively are both 25/10/2013 as they default to today's date. I have set up the dummy file I am testing to ensure it will provide data with this date range. Also for clarficiation I would like the search dates to be INCLUSIVE.

like image 603
Scott Avatar asked Oct 25 '13 07:10

Scott


People also ask

How can I get data between specific dates in SQL?

SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';

How do I pass a date range in SQL query?

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2000-01-01 00:00:00' AND '2002-09-18 12:00:00';

Can we use between operator for date in SQL?

The SQL BETWEEN OperatorThe values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

How can I get records between two dates in MySQL?

Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database.


1 Answers

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 + "' "
like image 113
Francis Avatar answered Sep 20 '22 07:09

Francis