CODEWITHSUNDEEP
javaoracle-databaseweblogic
Subinraj.S
Subinraj.S

Reputation: 43

java.sql.SQLDataException: ORA-01843 while deploying in Weblogic

I am trying to perform a select operation on my oracle db. I have kept my queries in a property file in my application which is as follows:

SELECT EMPLOYEE FROM EMPLOYEETABLE WHERE
JOINING_DATETIME >= ?

The JOINING_DATETIME is a timestamp column in oracle db and its value looks like 04-03-20 07:12:27.150000000 PM

I am supplying the value for JOINING_DATETIME as a String from my application like 04-03-20 05:41:52 PM and it works very well and do the necessary as expected while running in Tomcat servers.

But the problem is when the same has been deployed into Weblogic application I'm getting the error:

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

Could someone please help me out with this?

Upvotes: 0

Views: 189

Answers (2)

Lennart
Lennart

Reputation: 91

Supply the value for JOINING_DATETIME as a java.sql.Timestamp instead of a String.

Upvotes: 3

Popeye
Popeye

Reputation: 35900

You need to convert the string to timestamp using the proper format in your query as follows:

SELECT EMPLOYEE
  FROM EMPLOYEETABLE
 WHERE JOINING_DATETIME >= TO_TIMESTAMP(?,'DD-MM-RR HH:MI:SS.FF AM');

Update:

Regarding your doubt of AM/PM:

SQL> select TO_TIMESTAMP('04-03-20 07:12:27.150000000 PM','DD-MM-RR HH:MI:SS.FF AM') from dual;
TO_TIMESTAMP('04-03-2007:12:27.150000000PM','DD-MM-RRHH:MI:SS.FFAM')
---------------------------------------------------------------------------
04-MAR-20 07.12.27.150000000 PM

SQL> select TO_TIMESTAMP('04-03-20 07:12:27.150000000 AM','DD-MM-RR HH:MI:SS.FF AM') from dual;

TO_TIMESTAMP('04-03-2007:12:27.150000000AM','DD-MM-RRHH:MI:SS.FFAM')
---------------------------------------------------------------------------
04-MAR-20 07.12.27.150000000 AM

SQL>

Upvotes: 1

Related Questions