It’s SQLDeveloper

If I run the following in SQL Developer:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;
begin
dbms_session.set_nls('nls_date_format','''DD-MON-YY''');
end;
/
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;

Here’s the output from SQL Developer:
alter session set succeeded.

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  YYYY-MM-DD HH24:MI:SS                                           

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

anonymous block completed

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  DD-MON-YY                                                        <– This proves it’s not the database.

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

So maybe SQLDeveloper is caching NLS parameters?

Advertisements

3 Responses to “It’s SQLDeveloper”

  1. Gary Says:

    It’s sort of a demarcation issue.
    The NLS_DATE_FORMAT is used as the default for implicit datatype conversions within the database. However, when you SELECT SYSDATE FROM DUAL, you are selecting a date, and there’s no datatype conversion in the database. [SELECT TO_CHAR(sysdate) FROM DUAL would have the database doing the conversion and that ALWAYS knows the NLS_DATE_FORMAT.]
    What the client tool (in this case, SQL Developer) gets is seven bytes of ‘date’, which it converts into something human-readable using its own set of rules.
    SQL Developer tries to be useful by ‘eavesdropping’ on your instructions to the database about what your preferred date format is. SQL*Plus seems to have a different way of finding out what your NLS_DATE_FORMAT is (I’m guessing it is tied to the use of SQL*Net rather than SQL Developer’s thin JDBC connection).

  2. Dong Says:

    I can agree that for any production code, one should always use to_char for explicit data type conversion when needed. However, that doesn’t mean client tool can be excused for not handling NLS parameters correctly.
    Eavesdropping doesn’t work and never will.

  3. mallik8 Says:

    I havfe similar issue. My database server is set to eastern time zone and when I query the sysdate in sqlplus always shows the EDT time.
    But from sql developer it display Central time which an hour behind. system date on the system where sql develope is running also set to EDT. Anyone have any idea why this is happening.

    Thanks
    MG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: