NEW_TIME returns the date and time in timt zone ‘tz2’ based on the time zone ‘tz1’ and the date argument ‘the_date’.
Syntax:
NEW_TIME(the_date, tz1, tz2)
You must set the NLS_DATE_FORMAT parameter to a 24-hour format before you execute the NEW_TIME function.
e.g. ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’
The timezone arguments can be any of the following strings:
AST,ADT: Atlantic Standard or Daylight Time
BST,BDT: Bering Standard or Daylight Time
CST,CDT: Central Standard or Daylight Time
EST,EDT: Eastern Standard or Daylight Time
GMT: Greenwich Mean Time
HST,HDT: Alaska-Hawaii Standard Time or Daylight Time
MST,MDT: Mountain Standard or Daylight Time
NST: Newfoundland Standard Time
PST,PDT: Pacific Standard or Daylight Time
YST, YDT: Yukon Standard or Daylight Time
Example:
SELECT NEW_TIME(TO_DATE(’10-23-04 16:00:00′,’MM-DD-YY HH24:MI:SS’), ‘EST, ‘PST’) as RESULT from DUAL;
RESULT
————————
23-OCT-2004 13:00:00