Oracle PL/SQL Tutorial/Date Timestamp Functions/NEW TIME

Converting a Datetime from One Time Zone to Another with NEW_TIME() function

   <source lang="sql">

SQL> SQL> SELECT TO_CHAR(NEW_TIME(TO_DATE("25-MAY-2006 19:45",

 2    "DD-MON-YYYY HH24:MI"), "PST", "EST"), "DD-MON-YYYY HH24:MI")
 3  FROM dual;

TO_CHAR(NEW_TIME(

---

25-MAY-2006 22:45 SQL></source>

The NEW_TIME Function

The NEW_TIME function finds out the time in the time zones passing the date and time of the first zone, and specifying the second zone.

The Syntax for the NEW_TIME Function

   <source lang="sql">

NEW_TIME(input_date and time,time_zone1,time_zone2)</source>

What are the valid time zones

Time Zone Abbreviation Passed Time Zone Description AST Atlantic Standard Time ADT Atlantic Daylight Saving Time BST Bering Standard Time BDT Bering Daylight Saving Time CST Central Standard Time CDT Central Daylight Saving Time EST Eastern Standard Time EDT Eastern Daylight Saving Time GMT Greenwich Mean Time (Date Line!) HST Alaska-Hawaii Standard Time HDT Alaska-Hawaii Daylight Saving Time MST Mountain Standard Time MDT Mountain Daylight Saving Time NST Newfoundland Standard Time PST Pacific Standard Time PDT Pacific Daylight Saving Time YST Yukon Standard Time YDT Yukon Daylight Saving Time

Time Change from Chicago to Los Angeles

   <source lang="sql">

SQL> SELECT TO_CHAR(NEW_TIME(TO_DATE("060299 01:00:00 AM",

 2             "MMDDYY HH:MI:SS AM"),
 3             "CDT","PDT"), "DD-MON-YY HH:MI:SS AM") "Central to Pacific"
 4        from DUAL;

Central to Pacific

---

01-JUN-99 11:00:00 PM SQL> SQL></source>