1 2 Previous Next 23 Replies Latest reply on Apr 19, 2013 12:10 PM by EdStevens

    How to change NLS_DATE_FORMAT

    577696
      Hi all

      I unable to change the NLS_DATE_FORMAT='YYYY/MM/DD'. My default setting is
      nls_language = AMERICAN
      nls_territory = AMERICA
      nls_date_format =
      nls_length_semantics = BYTE
      nls_nchar_conv_excp = FALSE

      I am working Oracle 10g rel 1 in Windows XP.

      Awaiting your suggestions.

      Regards,

      Vijay Kumar
        • 1. Re: How to change NLS_DATE_FORMAT
          Flake
          Hi vijay kumar,

          You can alter the format at session level using the ALTER SESSION command as follows:

          ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';

          and check the format using

          SELECT SYSDATE FROM DUAL;

          ...
          • 2. Re: How to change NLS_DATE_FORMAT
            577696
            Thank Flake. But I want change the format in the database level. The change should be affected permanently. sorry to wrongly pose the question.
            • 3. Re: How to change NLS_DATE_FORMAT
              NavneetU
              Hi,
              For this you have to make changes in your parameter file and restart your server.

              Navneet
              • 4. Re: How to change NLS_DATE_FORMAT
                Flake
                Hi Vijay,

                The date format at the Server level is specified by adding parameter in the INIT.ORA file. Add the following if it doesn't exist, as follows:

                NLS_DATE_FORMAT='<format>'

                and I suppose you need to bounce the server to bring the effect.

                Regards,
                ...
                • 5. Re: How to change NLS_DATE_FORMAT
                  577696
                  After creating pfile from spfile,I changed the nls_date_format='YYYY-MM-DD' in the pfile and then recreated the spfile from pfile. Then I started the database. But I find no change when I issue the command select sysdate from dual even though the parameter settings is changed when I say SHOW PARAMETER nls.

                  What else I need to change to make the date format in 'YYYY-MM-DD' persistent.
                  • 6. Re: How to change NLS_DATE_FORMAT
                    Dom Brooks
                    You have the NLS_DATE_FORMAT at the system level, and the NLS_DATE_FORMAT at the session level.

                    When your client issues a select statement, it must have a different format.

                    Check nls_session_parameters.

                    Then it's just a case of changing your client settings/environment variables.

                    But you can't force all clients to have a certain setting. A date is a date is a date regardless of settings. If you want a date to always display in a certain format from a piece of code, then you're going to have to use TO_CHAR in your code with the explicit format you desire.

                    Alternatively, you could use a logon trigger to alter every session's nls_date_format but do you really want to do this?

                    Message was edited by:
                    dombrooks
                    • 7. Re: How to change NLS_DATE_FORMAT
                      577696
                      Thanks dombrooks for your suggestions. Actually our application which was in MySQL database is migrated to oracle 10g. They had been using Hibernate Query Language in Java to access the database. Since in oracle default date format is dd-mon-yyyy. Do I have to ask the developers to change the application coding for date format which is 'yyyy-mm-dd'. If not then what else I have to do. because in server even after changing parameter file the date is displayed in its default format ie, 'dd-mon-yyyy'

                      Awaiting suggestions.
                      • 8. Re: How to change NLS_DATE_FORMAT
                        Dom Brooks
                        What exactly is the problem?

                        I would assume that Java and Hibernate are dealing in dates - selecting, inserting & updating dates - and that they are displaying dates in whatever format they choose. No?

                        Also, it might be that your java application is connecting with a date format of 'yyyy-mm-dd' but that when you are connecting and testing, your client - sqlplus, I presume - has a different setting.

                        You shouldn't need to ask the developers to change anything.

                        But what exactly is the root problem? i.e. why do you feel that the format needs to be changed? Are your table columns not dates? Is the application not dealing in dates?
                        • 9. Re: How to change NLS_DATE_FORMAT
                          577696
                          why do you feel that the format needs to be changed? Are your table columns not dates? Is the application not dealing in dates
                          MySQL supports 'yyyy-mm-dd' format. when it comes to oracle database code application code for inserting records of date fields is 'yyyy-mm-dd' format as string. which is conflicting.
                          Now I created created year(), month(), date_format() function in oracle which is used as the method in dialect in java to take year, month and date separately. Still Can I force the server parameter file set the date format in 'YYYY-MM-DD' to make changes persistent.

                          Any suggestions appreciated.
                          • 10. Re: How to change NLS_DATE_FORMAT
                            NavneetU
                            I think Vijay, if you set the nls parameter in your INIT.ora file, then your problem is solved. Just try it once.

                            Navneet
                            • 11. Re: How to change NLS_DATE_FORMAT
                              Dom Brooks
                              If you can avoid it you shouldn't be passing about strings as dates.

                              Oracle knows about dates, Java knows about dates, they can talk to each other using a variety of date or timestamp variations.

                              You really shouldn't need to any of this year(), month(), date_format() stuff.

                              If you're application really must pass in strings, then what is most important is the session settings of your java app rather than the server setting. If your app doesn't specify a setting, then it will inherit the server setting. If your application does use explicit formats, then your server setting is pretty much irrelevant.
                              • 12. Re: How to change NLS_DATE_FORMAT
                                NavneetU
                                Hi,
                                You can instruct your developers regarding the database date format and they can change them accordingly. Because if you cahnge at the database level, There can be some other applicartions or some conditions which will then become invalid.


                                Navneet
                                • 13. Re: How to change NLS_DATE_FORMAT
                                  577696
                                  if you set the nls parameter in your INIT.ora file, then your problem is solved. Just try it once.
                                  Please read the thread carefully. I have already done it and also explained the steps how I have done it. But it not affecting in any way. only alter session is working which is not my objective.
                                  • 14. Re: How to change NLS_DATE_FORMAT
                                    NavneetU
                                    if you set the nls parameter in your INIT.ora file, then your problem is solved. Just try it once.
                                    Please read the thread carefully. I have already done it and also explained the steps how I have done it. But it not affecting in any way. only alter session is working which is not my objective.
                                    You said you did that and checked the sysdate , its not showwing that format as nls_date_format shos.

                                    But said that systdate will show the format specified by the nls parameters.?

                                    Nls_date_format is set so the date will be stored in the format specified by the parameter not for the sysdate function. It always show in dd-mon-yy format.

                                    Navneet
                                    1 2 Previous Next