Toad World® Forums

How is TZ formatting controlled in script (F5) output?

Hey all,

Of course I missed a Scheduler configuration on my pretty new databases, so my jobs are an hour off after the weekend. While troubleshooting, I ran this script:

set pagesize 4000 linesize 200
ALTER SESSION SET nls_timestamp_tz_format ='YYYY/MM/DD HH24:MI:SS TZR TZD';
SELECT parameter,VALUE FROM v$nls_parameters;
SELECT DBMS_SCHEDULER.stime stime FROM DUAL;
SELECT TO_CHAR(DBMS_SCHEDULER.stime,'YYYY/MM/DD HH24:MI:SS TZR TZD') stime_formatted FROM DUAL;

The output on my 18XE database is:

Session altered.

PARAMETER                                                        VALUE                                                           
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     AMERICAN                                                        
NLS_TERRITORY                                                    AMERICA                                                         
NLS_CURRENCY                                                     $                                                               
NLS_ISO_CURRENCY                                                 AMERICA                                                         
NLS_NUMERIC_CHARACTERS                                           .,                                                              
NLS_CALENDAR                                                     GREGORIAN                                                       
NLS_DATE_FORMAT                                                  DD-MON-RR                                                       
NLS_DATE_LANGUAGE                                                AMERICAN                                                        
NLS_CHARACTERSET                                                 AL32UTF8                                                        
NLS_SORT                                                         BINARY                                                          
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM                                                  
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM                                        
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR                                              
NLS_TIMESTAMP_TZ_FORMAT                                          YYYY/MM/DD HH24:MI:SS TZR TZD                                   
NLS_DUAL_CURRENCY                                                $                                                               
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                       
NLS_COMP                                                         BINARY                                                          
NLS_LENGTH_SEMANTICS                                             BYTE                                                            
NLS_NCHAR_CONV_EXCP                                              FALSE                                                           

19 rows selected.

STIME                               
------------------------------------
11/03/2020 07:53:29.566240000 -06:00
1 row selected.

STIME_FORMATTED                                            
-----------------------------------------------------------
2020/11/03 07:53:29 US/CENTRAL CST                         
1 row selected.

So, what controls how the STIME (datatype of TIMESTAMP WITH TIME ZONE) is formatted? In SQL*Plus it's displayed the same as the TO_CHAR version, and I can't seem to find a setting in Toad that would adjust this.

Thanks!
Rich

Hi Rich,

Our oracle access layer doesn't provide an easy way to change this. The date/time portion comes from windows settings (short time format). The timezone info is hard coded. :frowning:

-John

Hey John,

It's all good. Now I know, and knowing is half the battle™. :slight_smile:

Thanks!
Rich