Unable to query on date condition in specific range

Question

I want to fetch the record from HSQL database and query is as below:

ao.find(Issueda.class, Query.select().where("user=? AND TO_DATE(START_TIME, 'yyyy/MM/dd')>=     TO_DATE(?,'yyyy/MM/dd') AND TO_DATE(END_TIME, 'yyyy/MM/dd') <= TO_DATE(?,'yyyy/MM/dd')",user,parsedDate,parsedDate))

Now, it gives me an error that "To_DATE" is not valid keyword as below:

at java.lang.Thread.run(Thread.java:662)
 Caused by: java.sql.SQLException: Unexpected token: TO_DATE in statement [SELECT * FROM    
PUBLIC.AO_0371A8_ISSUE_da WHERE user=? AND TO_DATE(START_TIME, 'yyyy/MM/dd')>= TO_DATE(?,'yyyy/MM/dd') AND    TO_DATE(END_TIME, 'yyyy/MM/dd') <= TO_DATE(?,'yyyy/MM/dd')]

If i remove "To_date" then i am not getting correct result data from data and return null only even though data are existed in database. In database date field value's format is "2013-05-15 00:00:00.000000000"

Can any one plz share me any alternative to query to database ??

Answer 1

You don't need TO_DATE for this query. TO_DATE is for converting dates that are in String format.

It seems from your comments you want one parameter to be between the two dates, or outside the two dates.

The correct SQL for this type of query is like this:

This returns dates between start and end, inclusive:

WHERE USER=? AND ? BETWEEN CAST(START_TIME AS DATE) AND CAST(END_TIME AS DATE)

This returns dates outside start and end:

WHERE USER=? AND ? NOT BETWEEN CAST(START_TIME AS DATE) AND CAST(END_TIME AS DATE)

For the above queries, you use a java.sql.Data object with PreparedStatement.setDate(colIndex)

Now if the date you want to compare is a String in a format such as '2013/05/20' then you need TO_DATE(?,'yyyy/MM/dd') instead of the parameter. If the date is a String in standard format such as '2013-05-20' then you can use CAST(? AS DATE) instead of the parameter.

I assume your database table columns START_TIME and END_TIME are defined as of TIMESTAMP type, but it should work for VARCHAR(N) or CHAR(N) types.