JDBC Oracle date format

Hi, guys. I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.

Can someone give me some lights on this?

A DATE does not have a format. It's sent as binary data from the server to the driver and the driver converts into a java.sql.Date (or java.sql.Timestamp).

It's the client that does the formatting. So check the manual of the tool you are using if you can change the display format of dates there.

JDBC does not do any "formatting" at all.

I am not completely sure about this. I use the same clients to connect other Oracle 10 databases and the date format is OK. It is all the same, the only difference is the driver using to connect, so I am pretty sure the JDBC driver should have something somewhere to change date output format. As you said, the driver should have something where I can tell the output format for the java.sql.Date or maybe somewhere to tell it to use Timestamp.

Well, the client has an option called DATATYPE FORMAT per DRIVER. So I chose JDBC and DATE added my format. It was something of both, the client and the driver. The other format for other connections were OK because I was not using JDBC.

I have done a fair amount of JDBC programming in the last 12 years and believe me: the driver does not do any output formatting. It's all "binary" inside the driver (as a matter of fact the internal representation for a java.sql.Date or java.sql.Timestamp is a long - without any format attached)

That's not what I said. I said the client application (Squirrel, Aqua Studio) should have an option to format a date or timestamp. Because the application is responsible for converting the internal java.sql.Date representation into a String.

There is some logic in the driver but only to cope with badly written applications that request a (binary) java.sql.Date to to be returned as a String. But I'd consider such a program to be broken.

Yes, after reading and trying with some other clients I got to admit you were right. Thank you for your assistance, I was able to find the solution to this. Regards.