Re: Oracle nls_date_format

On 10/19/07, Frank Brendel <frank.brendel@xxxxxxxxxxx> wrote:
> Hello,
>
> no oracle experts out there?
>
> Anyway I've played a little with java and wrote a script to see wether it's a
> JDBC or SQuirreL problem.
>
> import java.sql.*;
> class dbAccess {
>   public static void main (String args []) throws SQLException
>   {
>         DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
>         Connection conn = DriverManager.getConnection
> ("jdbc:oracle:thin:@XXXX", "xxxxxx", "xxxxx");
>         Statement stmt = conn.createStatement();
>         ResultSet rset = stmt.executeQuery("select sysdate from dual");
>         rset.next();
>         System.out.println (rset.getString(1));
>         stmt.close();
>   }
> }
>
> $ java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin -classpath
> $ORACLE_HOME/jdbc/lib/ojdbc14.jar:./ dbAccess
> 2007-10-19 08:57:53.0
>
> As you can see the JDBC driver gives me the correct date format. I don't even
> have to set the NLS_DATE_FORMAT.
>
> SQuirreL shows me only the date "2007-10-19". But I'm missing the time.

Frank,

sysdate() function returns a column that appears to be a date
according to the driver.  So SQuirreL uses rs.getDate() in this case.
Without regard for standard SQL and now JDBC, Oracle stores time
information in it's DATE columns, instead of requiring you to use the
correct type - that is, a TIMESTAMP.

There is a work-around if you prefer not to use the "correct" data
type (timestamp) to store a time component along with the date.  In
Global Preferences -> DataType Controls -> Date section you can check
"treat DATE as TIMESTAMP" which will cause SQuirreL to retrieve all
java.sql.Date as java.sql.Timestamp instead.  Note however, that while
this works fine for Oracle, I've seen it not work so well for a
database that implements SQL according to the standard.  So be careful
with this setting if you also access non-Oracle databases.

Lastly, SQuirreL uses a type-safe mechanism for reading and writing
records in the database. Treating the Date like a String (getString)
would break down the type-safety that SQuirreL relies on.

Rob

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/