CODEWITHSUNDEEP
springoracle-database
αƞjiβ
αƞjiβ

Reputation: 3246

ORA-01843 error when using Date in Spring JdbcTemplate

After looking at most possible solutions I am still getting this exception: 

ORA-01843: not a valid month

My query and query call look like this

String sql = "select * from my_view "
           + "where TO_DATE(?, 'MM/DD/YYYY', 'NLS_DATE_LANGUAGE = American') "
                  + "between enroll_date and suspend_date "
           + "order by last_name, first_name";

return new JdbcTemplate(dataSource)
        .query(sql, 
               new Object[] { new java.sql.Date(dateOfCall.getTime()) }, 
               new BeanPropertyRowMapper<MyDto>(MyDto.class)
              );

dateOfCall is of type 

java.util.Date

Upvotes: 0

Views: 1018

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You bind it as date already. So, Oracle implicitly converts it into a String using TO_CHAR() in the format as in NLS_DATE_FORMAT.

Let's Assume NLS_DATE_FORMAT = 'DD/MM/YYYY'

Say Date 25th November 2014 gets converted implicitly as 25/11/2014.

Now a TO_DATE() into MM/DD/YYYY would probably error out because month is mapped with 25 here.

Unless you go for Abbreviated months/day in date string.. Just Date is enough. No need to NLS_DATE_LANGUAGE setting.

String sql = "select * from my_view "
           + "where ? "
                  + "between enroll_date and suspend_date "
           + "order by last_name, first_name";

Upvotes: 2

Related Questions