Oracle SQL: the Essential Reference

NEW_TIME

NEW_TIME(d, z1, z2)

Converts a date/time value from one time zone to another.

Parameters

d

Specifies the date/time value to convert.

z1

Specifies the source time zone. This parameter must be one of the time zone identifiers shown in Table 5-3.

z2

Specifies the destination time zone. This parameter must be one of the time zone identifiers shown in Table 5-3.

Table 5-3. Time Zone Identifiers

Time Zone Identifier	Time Zone
AST	Atlantic Standard Time
ADT	Atlantic Daylight Time
BST	Bering Standard Time
BDT	Bering Daylight Time
CST	Central Standard Time
CDT	Central Daylight Time
EST	Eastern Standard Time
EDT	Eastern Daylight Time
GMT	Greenwich Mean Time
HST	Alaska-Hawaii Standard Time
HDT	Alaska-Hawaii Daylight Time
MST	Mountain Standard Time
MDT	Mountain Daylight Time
NST	Newfoundland Standard Time
PST	Pacific Standard Time
PDT	Pacific Daylight Time
YST	Yukon Standard Time
YDT	Yukon Daylight Time

Example

The following example demonstrates that 8:00 A.M. Pacific Standard Time is equivalent to 11:00 A.M. Eastern Standard Time. The ALTER SESSION command alters the default date format to cause the time of day to be displayed along with the date:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-Mon-yyyy hh:mi am';

Session altered.

SQL> SELECT NEW_TIME(TO_DATE('8-May-2000 8:00 am','dd-mon-yyyy hh:mi am'),
  2                  'PST','EST') 
  3  FROM dual;

NEW_TIME(TO_DATE('8-
--------------------
08-May-2000 11:00 am