The problem statement is to convert any database local time zone to UTC timezone
The format should be I've achieved this format in one of my dev environment but when same code ran on different environment it superceeded "Z" part
With all wonders i took look at NLS parameters for date and time settings but couldn't make myself clear about this and I am really confused now.
I am sharing snippet of both database
1) Successfully Converted in DB1_A Oracle 12c
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> select TO_CHAR(SYS_EXTRACT_UTC(systimestamp), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;
TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'YYYY-MM-DDTHH24:MI:SSZ')
---------------------------------------------------------------------------
2017-04-18T11:16:40Z
SQL> select * from v$nls_parameters where parameter like '%DATE%' or parameter like '%TIME%';
PARAMETER VALUE
---------------------------------------------------------------- -----------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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 DD-MON-RR HH.MI.SSXFF AM TZR
6 rows selected.
SQL>
2) Confused with DB_B results Oracle 9i
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> select TO_CHAR(SYS_EXTRACT_UTC(systimestamp), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;
TO_CHAR(SYS_EXTRACT_
--------------------
2017-04-18T11:17:51
SQL> select * from v$nls_parameters where parameter like '%DATE%' or parameter like '%TIME%';
PARAMETER VALUE
---------------------------------------------------------------- -----------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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 DD-MON-RR HH.MI.SSXFF AM TZR
6 rows selected.
SQL>
What I suspect is that if 9i have behaviour of suppressing "Z" part then maybe I can concatenate "Z" at the end of the converted time but not in 12c