Spring JDBC Template SQL-Query filtered by date

Question

I need to get filter my query by two dates. This is working when I query for it with the Oracle SQL Developer or right out of Eclipse:

select * from TABLE where rechnungs_eingang >= '06.08.2012' AND rechnungs_eingang <= '06.08.2015')

But if I try to get the data via my webservice I've implemented with Spring 4 I get this exception:

Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select * from TABLE where rechnungs_eingang >= '06.08.2012' AND rechnungs_eingang <= '05.05.2014')]; ORA-01843: not a valid month
; nested exception is java.sql.SQLException: ORA-01843: not a valid month

What and why is happening here?

Answer 1

You need to use the to_date function and provide a date format mask if you pass in a string value in the query. Like this: to_date('06.08.2012', 'DD.MM.YYYY').

The reason why you got this working in SQLDeveloper without providing a date format is likely that the default format used in your locale is 'DD.MM.YYYY' and the implicit VARCHAR->DATE conversion happens correctly.