CODEWITHSUNDEEP
javasqlbetweensql-date-functions
Nick Karaolis
Nick Karaolis

Reputation: 137

SQL between two dates statement using java string

I'm trying to collect data from a table and then run a between two dates statement. The statement is created in a java string and then used by a resultSet variable using statement.executeQuery() with the string as the parameter. This is how I create the string for the two dates:

Calendar today = Calendar.getInstance();
        int month = today.get(Calendar.MONTH);
        int year = today.get(Calendar.YEAR);
        int endOfMonth = today.getActualMaximum(Calendar.DATE);
        String sql;
        if (month < 10) {
            sql = "SELECT * FROM ORDERS WHERE DATE BETWEEN " + "#0" + month + "/01/" + year + "#" + " AND " + "#0" + month + "/" + endOfMonth + "/" + year + "#";

And when I print line this string the result is this:

SELECT * FROM ORDERS WHERE DATE BETWEEN #03/01/2015# AND #03/30/2015#

However, when i run this string through the executeQuery method this error occurs:

java.sql.SQLSyntaxErrorException: Lexical error at line 1, column 41.  Encountered: "#" (35), after : "".

Upvotes: 0

Views: 3393

Answers (2)

Anand
Anand

Reputation: 853

Try this:

  Calendar cal= Calendar.getInstance(); //Get the current date
  SimpleDateFormat formatter= new SimpleDateFormat("yyyy/MMM/dd"); //format it as per your requirement
  String today = formatter.format(cal.getTime());
  String lastday = formatter.format(cal.getActualMaximum(Calendar.DATE));

  String sql;
  sql = "SELECT * FROM ORDERS WHERE DATE BETWEEN " + CONVERT(Char(10), today,112) + " AND " + CONVERT(Char(10), lastday ,112)

Upvotes: 1

legohead
legohead

Reputation: 540

In SQL the dates need to be contained inside quotes ' rather then hashtags #.

Once you change them this query should work.

SELECT * FROM ORDERS WHERE DATE BETWEEN '03/01/2015' AND '03/30/2015'

Rather than

SELECT * FROM ORDERS WHERE DATE BETWEEN #03/01/2015# AND #03/30/2015#

Upvotes: 3

Related Questions