ORA-01843: not a valid month JPA

Question

I am using JPA 1 and have a native query executing the following statement:

select NVL (max (x.some), 0) from (select count (1) as some, service from some_table Where trunc    (fh_report) = '09 / 01/2015 'group by service) x

marking the error is:

java.sql.SQLDataException: ORA-01843: not a valid month

However, if I copy the sentence in ORACLE ide this runs perfectly, I have two databases, one for testing and one for production, production marks this mistake and in testing dont.

I want to know also, what is better?? include the dates into string and then pass it to jpa like this?

String sql = "select NVL (max (x.some), 0) from (select count (1) as some, service from some_table Where trunc    (fh_report) = '09 / 01/2015 'group by service) x ";

Query query = em.createNativeQuery(sql.toString());

or pass the date value as parameter?

This could be the reason from error?

Thanks a lot.

Answer 1

trunc    (fh_report) = '09 / 01/2015 '

If you're going to TRUNC() a date, the resulting value should be "DD-MON-YYYY" as specified in the documentation.

I imagine if you changed your = text to

trunc    (fh_report) = '01-SEP-15'

it should work.

However, I am confused that you said it works in your IDE. I can't imagine it working unless you have different databases or versions.

Answer 2

Implicit date conversion may depend on both your client and your server settings. The same code will often run in one environment but not another.

One solution is to use an ANSI date literal, in the ISO 8601 format:

trunc(fh_report) = date '2015-09-01'

I'm guessing the original code uses the American date format. The advantage of ISO 8601 is that no person or computer needs to guess, it is unambiguous.