4 Replies Latest reply on Feb 2, 2012 8:27 PM by Syed Ullah

    nls_timestamp_format and systimestamp

    Syed Ullah
      Just wondering why systimestamp output is not influenced by the nls_timestamp_format session variable. sysdate output seems to change when nls_date_format is changed. Anyone knows why the behavior differs?
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE    11.2.0.2.0      Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      
      SQL> SELECT SYSDATE FROM DUAL;
      
      SYSDATE
      ---------
      02-FEB-12
      
      SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
      
      Session altered.
      
      SQL> SELECT SYSDATE FROM DUAL;
      
      SYSDATE
      ----------
      2012-02-02
      
      SQL> SELECT SYSTIMESTAMP FROM DUAL;
      
      SYSTIMESTAMP
      ---------------------------------------------------------------------------
      02-FEB-12 02.03.13.441081 PM -06:00
      
      SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
      
      Session altered.
      
      SQL> SELECT SYSTIMESTAMP FROM DUAL;
      
      SYSTIMESTAMP
      ---------------------------------------------------------------------------
      02-FEB-12 02.03.14.785289 PM -06:00
        • 1. Re: nls_timestamp_format and systimestamp
          Rene Argento
          When you select SYSTIMESTAMP the data type returned is TIMESTAMP WITH TIME ZONE.

          You need to alter the NLS_TIMESTAMP_TZ_FORMAT to see the changes.
          SELECT systimestamp FROM dual;
          
          SYSTIMESTAMP
          ---------------------------------------------------------------------------
          02/02/12 18.21.15,541000 -02:00
          
          
          ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
          
          
          SELECT systimestamp FROM dual;
          
          SYSTIMESTAMP
          ---------------------------------------------------------------------------
          2012-02-02 06.21.41.355289
          • 2. Re: nls_timestamp_format and systimestamp
            908002
            ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';

            Session altered.
            • 3. Re: nls_timestamp_format and systimestamp
              Centinul
              It's because (per the docs) the return type of SYSTIMESTAMP is not a TIMESTAMP, but:
              SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
              SQL> SELECT systimestamp FROM DUAL;
              
              SYSTIMESTAMP
              ---------------------------------------------------------------------------
              02-FEB-12 03.24.37.812747 PM -05:00
              
              SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SSXFF TZR';
              
              Session altered.
              
              SQL> SELECT systimestamp FROM DUAL;
              
              SYSTIMESTAMP
              ---------------------------------------------------------------------------
              2012/02/02 15:25:17.039748 -05:00
              • 4. Re: nls_timestamp_format and systimestamp
                Syed Ullah
                Makes sense. Thanks!