4 Replies Latest reply: Mar 26, 2007 3:44 AM by Paul M. RSS

    How to change default date format

    537728
      Every time I open SQL*Plus I launch that query:

      alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';

      Is there any way to modify default date format so I don't have to execute that query every time?
        • 1. Re: How to change default date format
          537728
          anyone?
          • 2. Re: How to change default date format
            Paul M.
            You can set environment variables at OS level. The following is on Linux :
            SQL> select sysdate from dual;

            SYSDATE
            ---------
            23-MAR-07

            SQL> exit
            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
            With the Partitioning, Real Application Clusters, OLAP and Data Mining options
            rac2-> export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
            rac2-> export NLS_DATE_FORMAT="dd/mon/yyyy hh24:mi:ss"
            rac2-> sqlplus / as sysdba

            SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 23 15:15:25 2007

            Copyright (c) 1982, 2005, Oracle.  All rights reserved.


            Connected to:
            Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
            With the Partitioning, Real Application Clusters, OLAP and Data Mining options

            SQL> select sysdate from dual;

            SYSDATE
            --------------------
            23/mar/2007 15:15:33

            SQL>
            • 3. Re: How to change default date format
              537728
              rac2-> export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
              rac2-> export NLS_DATE_FORMAT="dd/mon/yyyy hh24:mi:ss"
              Where do I type that? at cmd window? I am using Windows-XP
              • 4. Re: How to change default date format
                Paul M.
                Where do I type that? at cmd window?
                Yes :
                SQL> select sysdate from dual;

                SYSDATE
                ---------
                26-MAR-07

                SQL> exit
                Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Pro
                ction

                C:\>set NLS_DATE_FORMAT=dd/mon/yyyy hh24:mi:ss

                C:\>sqlplus / as sysdba

                SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 26 10:38:27 2007

                Copyright (c) 1982, 2005, Oracle.  All rights reserved.


                Connected to:
                Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

                SQL> select sysdate from dual;

                SYSDATE
                --------------------
                26/mar/2007 10:38:35

                SQL>
                If you need a permanent setting you can set it in the Registry, or as environment variable.