oracle error: not a valid month

Question

I am running an application on websphere 7.0 version which was newly setup at my QA env and getting below oracle error

SELECT MODIFIED_DATE 
FROM /*Query Builder Clause*/ abc 
WHERE  abc_CID = '1189' 
   AND NVL(to_date('2010-3-17 11.30.10.0', 'MM-dd-yyyy hh24:mi:ss'), SYSDATE)=decode(MODIFIED_DATE,null,SYSDATE,MODIFIED_DATE)

throws below exception

Error report:

SQL Error: ORA-01843: not a valid month 01843. 00000 - "not a valid month"

This exception pop up only with websphere. Also, i can't modify the code since same code is running on websphere at onsite. I tried to do websphere user.timezone setting as well, also i tried default ojdbc6.jar to higher version.Also same code works fine in jboss and weblogic. Please help me out to resolve this issue.

NVL(to_date('03-17-2010 11:30:10', 'MM-dd-yyyy hh24:mi:ss'),SYSDATE) — access_granted, Nov 30 '16 at 03:35
The format model (the second argument to the TO_DATE function) needs to *match* the actual format of the first argument. (The value `'2010-...'` doesn't match the format model `'MM-...'`) — spencer7593, Nov 30 '16 at 04:51

score 0 · Answer 1 · answered Nov 30 '16 at 04:39

0

Oracle does not store date type with milliseconds, which means you'll have to trim the last token. In such case your expression is:

NVL(to_date('2010-3-17 11.30.00', 'yyyy-mm-dd hh24.mi.ss'),SYSDATE)

You can also convert to a timestamp instead:

NVL(to_timestamp('2010-3-17 11.30.00.0', 'yyyy-mm-dd hh24.mi.ss.ff1'),SYSDATE)

answered Nov 30 '16 at 04:39

access_granted

1,807
20
25

I can't change the code, since same code is behaving well with websphere production env and other server. And we follow below for NVL(to_date('**11-30-17 11.30.10.0**', 'MM-dd-yyyy hh24:mi:ss') format. This is something websphere setting issue. – Prashant Pandit Nov 30 '16 at 06:50
Should be better `NVL(to_timestamp('2010-3-17 11.30.00.0', 'yyyy-mm-dd hh24.mi.ss.ff1'),SYSTIMESTAMP)` – Wernfried Domscheit Nov 30 '16 at 08:18

score 0 · Answer 2 · edited May 23 '17 at 12:00

The problem is that '2010-3-17 11.30.10.0' and 'MM-dd-yyyy hh24:mi:ss' don't match. I assume the other servers where this is working are in a different locale that uses the format MM-dd-yyyy. If you can't make any code changes to reformat the date you will need to change your jvm locale to match where it is working (or any locale that uses MM-dd-yyyy). See the below link for changing the jvm locale:

how do I set the default locale for my JVM?

oracle error: not a valid month

2 Answers2