#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3

    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?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    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 will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3
    Originally Posted by shammat
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3
    Originally Posted by jaimitoc30
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by jaimitoc30
    IIt 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
    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)

    Originally Posted by jaimitoc30
    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.
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    38
    Rep Power
    3
    Originally Posted by shammat
    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.

IMN logo majestic logo threadwatch logo seochat tools logo