1 2 Previous Next 23 Replies Latest reply on Apr 19, 2013 12:10 PM by EdStevens Go to original post
      • 15. Re: How to change NLS_DATE_FORMAT
        577696
        Nls_date_format is set so the date will be stored in the format specified by the parameter not for the sysdate function
        Thanks Navneet, I needed only this solution. I thought it will also show sysdate in that format. Now the date format conflict is resolved.
        • 16. Re: How to change NLS_DATE_FORMAT
          NavneetU
          I hope Your problem is really solved but i will recommend that the conversion should be done at the front hand or at the midlle tier intead at the database,

          But its fine as the issue has been solved.

          Cheers!
          Navneet
          • 17. Re: How to change NLS_DATE_FORMAT
            153119
            Vijay,

            There seems to be much confusion and misinformation in this thread.

            Fact: An Oracle client sessions ALWAYS overrides the format set at the database level.
            Fact: If you don't set the environment variable NLS_DATE_FORMAT (Unix) or the string value NLS_DATE_FORMAT in the registry (Windows), the default will be based upon the American territory, so dd-mon-yy.
            Solution:
            You need to set the format explicitly either on the client O/S (If you have an application server on the application server O/S) or by using a database after logon trigger issuing execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';

            This is the only way it is going to work, whatever other contributors state.

            --
            Sybrand Bakker
            Senior Oracle DBA
            • 18. Re: How to change NLS_DATE_FORMAT
              Dom Brooks
              The bottom line is that a java app dealing with an oracle database, if it's even half well written, doesn't need to do any date conversion other than is done internally.

              Having java passing over dates in strings of format xyz and having oracle expecting strings of format xyz is unnecessary.
              • 19. Re: How to change NLS_DATE_FORMAT
                577696
                Fact: If you don't set the environment variable NLS_DATE_FORMAT (Unix) or the string value NLS_DATE_FORMAT in the registry (Windows), the default will be based upon the American territory, so dd-mon-yy.
                Solution:
                You need to set the format explicitly either on the client O/S (If you have an application server on the application server O/S) or by using a database after logon trigger issuing execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';
                Thanks Sybrand. Its correct. We need to use logon trigger or make changes on the client O/S. In previous reply I without checking the actual facts told the issue is solved. Even though my issue is solved from developers points, but the thread for which I raised question was not solved.

                Regards
                Vijay Kumar
                • 20. Re: How to change NLS_DATE_FORMAT
                  Ravindra Malwal
                  hi,
                  use the below command i hope it will help to solve your problem.

                  alter system set nls_date_format='mm-bb-yyyy hh24:mi:ss' scope=spfile;

                  then restart your database.

                  check current date format.

                  SQL> select sysdate from dual;

                  SYSDATE
                  -------------------
                  19-04-2013 12:43:49

                  regards,
                  Ravindra Malwal
                  • 21. Re: How to change NLS_DATE_FORMAT
                    Ravindra Malwal
                    hi vijay,
                    use below command i hope it will help to change the nls_date_format default.
                    alter system set nls_date_format='mm-dd-yyyy hh24:mi:ss' scope=spfile;

                    restart your database

                    startup force;


                    SQL> select sysdate from dual;

                    SYSDATE
                    -------------------
                    19-04-2013 12:43:49

                    regards,
                    Ravindra malwal
                    • 22. Re: How to change NLS_DATE_FORMAT
                      SomeoneElse
                      use the below command i hope it will help to solve your problem.
                      I hope so too. He's been waiting 5 years.
                      • 23. Re: How to change NLS_DATE_FORMAT
                        EdStevens
                        user9179526 wrote:
                        hi,
                        use the below command i hope it will help to solve your problem.

                        alter system set nls_date_format='mm-bb-yyyy hh24:mi:ss' scope=spfile;
                        Besides the fact that you are answering a five-year old question, your suggestion is VERY unlikely to work.

                        Setting nls_date_format at the system is the weakest setting there is. It can be overridden at least 3 different ways by the client.

                        Actually, what you demonstrate actually makes a case for NOT depending on a system-level setting. If I write my code assuming date/char conversions based on the system level setting, I'm vulnerable to some DBA coming along and changing that setting because of something he read on the internet. Then my code breaks.
                        then restart your database.

                        check current date format.

                        SQL> select sysdate from dual;

                        SYSDATE
                        -------------------
                        19-04-2013 12:43:49

                        regards,
                        Ravindra Malwal
                        1 2 Previous Next