1

I am using PreparedStatement's setString method to set values for the start and end dates in a sql query.

String sql = "..... " AND tableA.time BETWEEN ? " +
                    " AND ?";

PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, startDate);
st.setString(2, endDate);

The values however are not being set. I understand that normally there is an equals sign: "tableA.member_id = ?" +"

How do I than call setString method when I am using a 'Between' operator in the sql statement? Hope someone can advise. Thank you.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
Sor
  • 11
  • 3

3 Answers3

2

See http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm

"BETWEEN" can be used with datetime data types. However, if tableA.time is a datetime field of some kind (Timestamp, etc.), using st.setString(...) won't work. You need to use setDate(dt) or setTimestamp(ts) instead. See Using setDate in PreparedStatement for more info.

Community
  • 1
  • 1
Matt Campbell
  • 1,967
  • 1
  • 22
  • 34
2

Use setDate.

Is your startDate and endDate a java.util.Date object?

If it is, you can use this code.

st.setDate(1, new java.sql.Date(startDate.getTime()));
st.setDate(2, new java.sql.Date(endDate.getTime()));

If it is not. Convert that first ro java.util.Date object.

Alvin Magalona
  • 771
  • 3
  • 13
1

BETWEEN expects its arguments to have a type that is compatible with the column being tested. Giving it string arguments for a date column won't work.

You could add a function to the SQL in order to convert the string to a date (I don't know what database you're using, this example uses Oracle's to_date function):

from tableA.time BETWEEN to_date(?, 'yyyy/mm/dd') AND to_date(?, 'yyyy/mm/dd')

Alternatively you could leave the SQL alone and use setDate on the PreparedStatement, like:

setDate(1, new SimpleDateFormat("yyyy/MM/dd").parse(startDate));

This second way is more usual, it has the advantage of avoiding having to add a date-conversion function (that may be non-standard) to your SQL.

But you would have to do the conversion on one side or the other. The database's SQL parser won't convert it for you. If the database took responsibility for the conversion and there was a mistake it could introduce data errors silently, the less error-prone alternative is for the application developer to tell the database how the date string should be converted.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
  • I am just curious as to why it would matter that it's not in date or datetime format. The reason is I am passing the entire query as a String value, so as long as the value in startDate, endDate is formated as YYYY/MM/DD or YYYY-MM-DD why would it be a problem? – Sor Oct 27 '14 at 04:53
  • @Sor: because the sql parser expects the types to match up, and it doesn't take on the responsibility for converting strings to dates, there are too many possible formats and it would be too error-prone. – Nathan Hughes Oct 27 '14 at 05:08