6 Replies Latest reply on Apr 8, 2019 7:08 AM by Jan de Rijke

    SQL developer implicit date conversion not using NLS_LANGUAGE?

    Jan de Rijke
      alter session set NLS_DATE_FORMAT ='dd fmmonth yyyy';
      select sysdate from dual;
      select to_char(sysdate) from dual;

      Session altered.



      26 march 2019



      26 maart 2019


      alter session set NLS_LAGUAGE=DUTCH (or any other language, or NLS_DATE_LANGUAGE) does not have any impact, select sysdate remais in english, while the to_char function uses the language. Strange, since select sysdate does respect the DATE_FORMAT.

      This is on sqldevelper 18.4, oracle XE 11c, java 8u144


      select * from nls_session_parameters;


      PARAMETER                      VALUE                                  

      ------------------------------ ----------------------------------------

      NLS_LANGUAGE                   DUTCH                                  

      NLS_TERRITORY                  BELGIUM                                

      NLS_CURRENCY                   €                                      

      NLS_ISO_CURRENCY               BELGIUM                                

      NLS_NUMERIC_CHARACTERS         ,.                                     

      NLS_CALENDAR                   GREGORIAN                              

      NLS_DATE_FORMAT                dd fmmonth yyyy                        

      NLS_DATE_LANGUAGE              DUTCH                                  

      NLS_SORT                       BINARY                                 

      NLS_TIME_FORMAT                HH24:MI:SSXFF                          

      NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF                 


      PARAMETER                      VALUE                                  

      ------------------------------ ----------------------------------------

      NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                      

      NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR             

      NLS_DUAL_CURRENCY              €                                      

      NLS_COMP                       BINARY                                 

      NLS_LENGTH_SEMANTICS           CHAR                                   

      NLS_NCHAR_CONV_EXCP            FALSE 


      Is this a bug or is it me?