Spring JDBCTemplate date format error on oracle query

Question

I want to write a query which returns list of result data in between two dates(including them).This is what I tested and the query returns with an error ORA-01830: date format picture ends before converting entire input string.

DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date from=df.parse("2015-05-27 12:12:12");
Date toDate=df.parse("2015-05-28 12:12:12");

//this returns ORA-01830: date format picture ends before converting entire input string
sfaTemplate.query("select * from my_requests where received_from=? and to_date(request_at)>=to_date(?) and to_date(request_at)<=to_date(?) ", new BeanPropertyRowMapper<SFAReload>(SFAReload.class), 12, from, toDate);

//but this works just change to_date(request_at)<=to_date(?) into to_date(request_at)>=to_date(?)
 sfaTemplate.query("select * from my_requests where received_from=? and to_date(request_at)>=to_date(?) and to_date(request_at)>=to_date(?) ", new BeanPropertyRowMapper<SFAReload>(SFAReload.class), 12, from, toDate);

changing the to_date(request_at) <= to_date(?) into to_date(request_at) >= to_date(?) makes query run.Can some one give me the reason?

Answer 1

you are using to_date(?) in your query but you are passing two date objects as parameters. to_date in oracle expects a string (and an optional format), so you must choose between

1. passing a Date object in jdbc template (as you are doing now) changing the query avoiding the to_date conversion and using for example to_date(request_at)<=to_date(?)

2. passing a String object (i.e. "2015-05-28 12:12:12")in jdbc template representing a date and using a to_date(?,'yyyy-mm-dd hh24:mi:ss') in the query.