1 2 Previous Next 16 Replies Latest reply: Jun 1, 2007 12:33 PM by 458726 RSS

    How to change the NLS_DATE_FORMAT permanently

    548091
      Hi,
      I have query that how to change the NLS_DATE_FORMAT permanently after the new database has been created.

      The new database was created with this parameter as “DD-MON-RR” and I want to change it to “DD.MM.YYYY” on a permanent basis.

      Looking forward to your prompt solution.
      Regards.
        • 1. Re: How to change the NLS_DATE_FORMAT permanently
          427828
          change it on your init.ora
          • 2. Re: How to change the NLS_DATE_FORMAT permanently
            548091
            Hi,
            What are the exact steps to follow before and after changing the NLS_DATE_FORMAT parameter. The database has already been created with a wrong format (DD-MON-RR), so i want to change it to DD.MM.YYYY permanently.
            I hopoe you are getting my point.
            Any more solutions.
            Regards.

            Message was edited by:
            user545088
            • 3. Re: How to change the NLS_DATE_FORMAT permanently
              AlokKumar
              If working 9i or greater, then use the below command to set date format.

              sql > alter system nls_date_format ='dd/mm/yy' scope=both ;

              see, how it goes.

              hare krishna
              Alok
              • 4. Re: How to change the NLS_DATE_FORMAT permanently
                548091
                Hi,
                I am using Oracle9i rel 9.0.1.1.1.
                I want to set this nls_date_format parameter on the server on a permanent basis.
                I have already created a new database with a wrong nls_date_format value. I want to change this value to a new value on a permanent basis.
                Will the command given by you work in this scenario.
                Any more solutions.
                regards.
                • 5. Re: How to change the NLS_DATE_FORMAT permanently
                  580491
                  If you use Windows OS, you need to add nls_date_format configuration on the registry.

                  Regards
                  Tom
                  • 6. Re: How to change the NLS_DATE_FORMAT permanently
                    548091
                    But this change will happen for all the databases on a particular server.
                    I just want to change the parameter for a particular database on that server. This is a new database which has already been created. I want to change in this new database only.
                    I hope you'll are getting my point.
                    Any more solutions.
                    Regards.
                    • 7. Re: How to change the NLS_DATE_FORMAT permanently
                      548091
                      Can anybody provide the exact solution at the earliest.
                      regards,.
                      • 8. Re: How to change the NLS_DATE_FORMAT permanently
                        AlokKumar
                        Did you try out my solution? just set nls_date_format at system level. And get back to us, If it din't get work.


                        hare krishna
                        Alok
                        • 9. Re: How to change the NLS_DATE_FORMAT permanently
                          580748
                          You can try to create an after log-on trigger with only one statement.


                          EXECUTE IMMEDIATE
                          'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD'' ';
                          • 10. Re: How to change the NLS_DATE_FORMAT permanently
                            12cdb
                            Try the solution suggested by Kishore above;

                            SQL> alter system nls_date_format ='dd/mm/yy' scope=both ;
                            then
                            SQL> create pfile from spfile ;
                            then
                            Restart the Database Server.

                            I hope this should be fine.
                            • 11. Re: How to change the NLS_DATE_FORMAT permanently
                              569701
                              are u guys sure that we can chage the NLS_DATE_FORMAT thru alter system ??

                              well even i also thot so.. but gave it a try...

                              SYS@insys >show parameters spfile

                              NAME TYPE VALUE
                              ------------------------------------ ----------- ------------------------------
                              spfile string /u01/app/oracle/oracle/product
                              /10.2.0/db_1/dbs/spfilein.o
                              ra

                              SYS@insys >alter system set NLS_DATE_FORMAT='dd/mm/yy' scope=both;
                              alter system set NLS_DATE_FORMAT='dd/mm/yy' scope=both
                              *
                              ERROR at line 1:
                              ORA-02096: specified initialization parameter is not modifiable with this option

                              SYS@insys >alter system set nls_date_format='dd/mm/yy' scope=spfile;
                              System altered.
                              SYS@insys >create pfile from spfile;
                              SYS@insys >startup pfile=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/initin.ora
                              SYS@insys >select sysdate from dual;
                              SYSDATE
                              ---------
                              01-JUN-07

                              STILL ?????


                              its in a 10g..

                              i did check out in the net too....
                              http://orafaq.com/parms/parm923.htm
                              seems we cannot change it thru ALTER SYSTEM

                              Rgds,
                              Jj

                              Message was edited by:
                              user566698
                              • 12. Re: How to change the NLS_DATE_FORMAT permanently
                                mpatzwahl
                                You can ONLY change the OUTPUT of the Date-Format by the following ways:

                                1. Set the Parameter in Windows in a Dos-Box:
                                dos> set nls_date_format=DD.MM.YYYY HH24:MI:SS
                                now start you Oracle Client Software (p.e Rman)
                                dos> rman target=/

                                1b. Set the Parameter in the Registry of the CLIENT (HKLM/Software/Oracle/<Oracle_HOME>
                                nls_date_format=DD.MM.YYYY HH24:MI:SS

                                1c. Set The Parameter in the System-Properties (where you find the temp-Directory)
                                I Only know the German-Name :Systemeigenschaften/Umgebungvariablen
                                Should be something like System-Properties / Environment Variables

                                2. Set the Parameter in Unix in a Shell (but it works only in this Shell)
                                export NLS_DATE_FORMAT=DD.MM.YYYY HH24:MI:SS

                                2b. Set the Parameter in the .login or .bashrc File

                                3. Change it in the Session
                                ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

                                THERE IS NOW WAY (that i know) TO CHANGE THE PARAMETER ON THE SERVERSIDE to get a special Date-Output.

                                If you change the init.ora Parameter nls_date_format it changes the INPUT-FORMAT of Date-Columns.

                                Marco Patzwahl
                                MuniQSoft GmbH
                                Munich-Unterhaching, Germany
                                • 13. Re: How to change the NLS_DATE_FORMAT permanently
                                  427828
                                  Thank you Marco
                                  Very brief and beautiful explanation
                                  • 14. Re: How to change the NLS_DATE_FORMAT permanently
                                    548091
                                    Hi,
                                    I tried your solution, but it did not work. Below are the steps which I followed for doing your solution:
                                    SQL> SHUTDOWN IMMEDIATE;
                                    SQL> STARTUP MOUNT;
                                    SQL> alter system set nls_date_format='DD.MM.YYYY' scope=both;
                                    SQL> SHUTDOWN IMMEDIATE;
                                    SQL> STARTUP;

                                    As per Oracle, this parameter cannot be changed. You can check the issys_modifiable column in v$parameter table, which gives whether any parameter is modifiable or not.
                                    Do you have any other solutions at the earliest.
                                    regards.
                                    1 2 Previous Next