7 Replies Latest reply on Jan 3, 2019 1:27 PM by Gaz in Oz

    SQL Developer wrong date format in result view

    MrBlackDemon

      Hello, in Struments -> Preferences -> Database -> NLS i setted all date formats, language and date language to ITALIAN

      PARAMETERVALUE
      NLS_LANGUAGEITALIAN
      NLS_TERRITORYITALY
      NLS_CURRENCY
      NLS_ISO_CURRENCYITALY
      NLS_NUMERIC_CHARACTERS,.
      NLS_CALENDARGREGORIAN
      NLS_DATE_FORMATDD-MON-RR
      NLS_DATE_LANGUAGEITALIAN
      NLS_SORTWEST_EUROPEAN
      NLS_TIME_FORMATHH24:MI:SSXFF
      NLS_TIMESTAMP_FORMATDD-MON-RR HH24:MI:SSXFF
      NLS_TIME_TZ_FORMATHH24:MI:SSXFF TZR
      NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH24:MI:SSXFF TZR
      NLS_DUAL_CURRENCY
      NLS_COMPBINARY
      NLS_LENGTH_SEMANTICSBYTE
      NLS_NCHAR_CONV_EXCPFALSE

       

      And after all this settings in the result query the format date was '02-JAN-19' instead of '02-GEN-19'

      how to solve that?

        • 1. Re: SQL Developer wrong date format in result view
          Glen Conway

          Just add this line to the sqldeveloper.conf file in your installation's sqldeveloper\sqldeveloper\bin directory:

          AddVMOption -Duser.language=it

           

          Edit:

          Actually, I see that date_format elements like MON, or even DL, do not respect that setting.

          Even doing  " alter session nls_date_language = 'ITALIAN' " does not work in either of sqlcl or SQL Developer 18.3,

          although doing it in SQL*Plus will give you the desired result.    So, sorry, it looks like this is a bug in the product.

          • 2. Re: SQL Developer wrong date format in result view
            Gaz in Oz

            What OS are you running sql developer on?

            What is your OS locale set to?

            What version of sql developer are you running?

            Take a look at Help -> About -> [Properties] tab -> search for lang. What does that show?

            On my windows 10 OS install of sqldeveloper my user.language is set to "en"...

            Manually changing Tools -> Preferences... -> Database -> NLS -> Date language from AMERICAN to ITALIAN works for me, in sqldev version 18.1.0.95.1630 , it correctly display GEN.

             

            A work-around, if you need one, would be to use TO_CHAR(<date>, 'DD-MON-YYYY', 'NLS_DATE_FORMAT = ITALIAN')

            For example:

            select to_char(sysdate, 'DD-MON-YYYY')                                native, -- my Date language: AMERICAN in sqldev.
                   to_char(sysdate, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = Italian') italian
            from   dual;
            
            NATIVE               ITALIAN
            -------------------- --------------------
            03-JAN-2019          03-GEN-2019
            

            Passing the third argument of TO_CHAR makes the date display client agnostic.

             

            Having said all that, it may be what ever you have set your sqldev settings to or it is being affected OS locale.

            • 3. Re: SQL Developer wrong date format in result view
              MrBlackDemon

              Adding the line 'AddVMOption -Duser.language=it' dos not resolve, waiting for a new release, may be it will be solved

              • 4. Re: SQL Developer wrong date format in result view
                MrBlackDemon

                OS: Windows 7 Pro (Italian)

                18.2 version of SQL Developer and all settings was setted to italian

                I saw that if i do the convert to_char whitout 'NLS_DATE_LANGUAGE' it do the correct parse.

                Trying to download the v18.1 of SQL Developer.

                With v4.0.3 this problem does not exist.

                • 5. Re: SQL Developer wrong date format in result view
                  Gaz in Oz

                  Thanks for the ansers...

                  OS: Windows 7 Pro (Italian)

                    so OS is windows 7 and in regional settings the language is set to Italian.

                   

                  18.2 version of SQL Developer and all settings was setted to italian

                  18.2. what? there are many more digits denoting the actual version. Help -> About... gives you that info.

                  Setting "all settings" to Italian huh? Show what you are doing/setting. with copy/paste of anything textual and  screenshots (*shudder*) of anything you can not copy/paste, by using the windows snipit tool or something. It is very unclear what you are actually doing. Hopefully upgrading/downgrading will "fix" it if it is an error with sqldev..

                   

                  I saw that if i do the convert to_char whitout 'NLS_DATE_LANGUAGE' it do the correct parse.

                  That makes no sense. If you use TO_CHAR(<date>, 'DD-MON-YYYY', 'NLS_DATE_FORMAT = Italian') it shows the wrong MON value?

                  Please post the sql statement and the output showing that.

                   

                  Trying to download the v18.1 of SQL Developer.

                  https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

                  From here you can download the latest Version 18.3.0.277.2354     October 10, 2018 (at time of writing) or keep clicking on "Previous Version" on the URL further down that page.

                   

                  With v4.0.3 this problem does not exist.

                  So it may well be an error with 18.2. Upgrade to latest and test.

                  • 6. Re: SQL Developer wrong date format in result view
                    MrBlackDemon

                    Thanks for the reply.

                     

                    Yes my OS was configured all to Italian (language, time, keyboard, ...)

                     

                    Settings of SqlDeveloper 18.3.0.277.2354

                    SqlDeveloperVersion.PNG

                    NLSSettings.PNG

                    LangProperties.PNG

                    All setted to 'ITALIAN'

                     

                    Here an example of execution of query:

                    select sysdate native, to_char(sysdate, 'DD-MON-YYYY') parse, to_char(sysdate, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = Italian') italian from dual;

                    Output:

                    NATIVE: 03-JAN-19 (WRONG)

                    PARSE: 03-GEN-2019 (CORRECT)

                    ITALIAN: 03-GEN-2019 (CORRECT)

                     

                    Tested with this SqlDeveloper versions:

                    - 18.3.0.277.2354

                    - 18.2.0.183.1748

                    - 18.1.0.095.1630

                    - 17.4.1.054.0712

                     

                    all this present same problem, but with version 4.0.3.16.84 (having same setting of attached images) this problem does not persist.

                    • 7. Re: SQL Developer wrong date format in result view
                      Gaz in Oz

                      Cool, thanks for that.

                      So it looks like an issue with later versions of sqldeveloper and DATE datatype "formatting".

                      You have a work around, how ever, you should raise a MOS SR at support.oracle.com, stating this is a bug, as Glen said in the first place, but now you have enough info to present a coherent bug report.