Oracle JDBC Thin Client behaviour concerning date data types and it's format.

[Richrard Cabangon]

Hi guys,
I'm just curious abouth this sql statement here
SQL Statement:

SELECT * FROM TABLE.PERSON WHERE COLUMN.DATE >= '2011-02-17 00:00:00'

Apparently this sql statement here does not work on Oracle client manager, since it always says "literal string does not match" error. But apparently it works on Oracle JDBC Thin Client when you execute this sql statement in the java statement. This kinda made me puzzled. What kind of formats does Oracla JDBC Thin Client work on date datatypes. I really would like to be clarified on this.

It really would be appreciated also if there's an article concerning date datatypes for Oracle JDBC. I was looking for one and I can't seem to find this topic on the net.

I'm sorry for the english, and thank you to those who would reply.

[Fatih Keles]

Hi Richrard,

Your sql statement involves implicit data type conversion. Your date string is converted to date before execution with some date format mask stored in NLS_DATE_FORMAT session value. Each database client so session declares it's date format mask. The correct and compatible version should be

SELECT * FROM TABLE.PERSON WHERE COLUMN.DATE >= to_date('2011-02-17','RRRR-MM-DD')

The error message is self explanatory. Suppose your session declares a format of DD-MON-YYYY than any date string that is to be converted to date without specifying the format should match NLS_DATE_FORMAT string.

In JDBC it is simple use PreparedStatement and setDate method, it would handle the issue.

So long story short,

use PreparedStatement and setDate in JDBC

use to_date('<your_date_string>','<your_date_format>') in SQL/PLSQL

Regards,
Fatih.

[Richrard Cabangon]

Hi Faith,

Thank you for your reply. Now I understand why my SQL statement works now on JDBC. I would like to have one more clarification please. My SQL Statement is issued using only Statement instead of PreparedStatement. Does this implicit conversion also works on Statement? Thanks in advance...

[Fatih Keles]

It does not matter if it is a Statement or PreparedStatement. If a table column is to be compared with some value -when you add a where clause to your query- then both sides of the equal sign must have the same data type if not then one of them is to be converted in order to compare them.

Suppose this senario

create table my_table(col1 varchar2(80)); insert into my_table values(12); insert into my_table values(999); insert into my_table values(75); select * from my_table where col1 > 15

The above code sample uses a table with a varchar column. It is fine to insert a number since it can be converted to varchar implicitly. So our select statement returns 2 rows.

insert into my_table values('X12'); select * from my_table where col1 > 15

But after inserting a varchar to table I get an "Invalid Number" error. Because Oracle tries to convert my_table.col1 to number in order to compare it to 15 in my select statement's where clause. It is similiar for date columns.

select 1 from dual where sysdate-10 > '01-JUN-2011'
This one works for me and returns 1

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'; select 1 from dual where sysdate-10 > '01-JUN-2011'

But after I change the format it gives : ORA-01858: a non-numeric character was found where a numeric was expected

select 1 from dual where sysdate-10 > to_date('01-JUN-2011','DD-MON-RRRR')
The above statement will always work regardless of session date format, because it supplies the format modifier.

[Richrard Cabangon]

Hi Faith,

Thanks for your answers!! More power to you! XD