• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / Oracle NEW_TIME Function

Oracle NEW_TIME Function

November 5, 2012 by techhoneyadmin

Oracle NEW_TIME FunctionOracle NEW_TIME Function is used to return the date in one time zone to a date in another time zone.


Oracle NEW_TIME Function Syntax

SELECT NEW_TIME(date,time_zone1, time_zone2)
FROM table_name;

Here in the above Oracle NEW_TIME Function Syntax:

  • ‘date’ is the date that has to be seen in another time zone.
  • ‘time_zone1’ is the time zone from where the passed date is to be converted
  • ‘time_zone2’ is the time zone to which the passed date is to be converted

Time_zone1 and time_zone2 can take the following values:

Value Explanation
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

Oracle NEW_TIME – Converting GMT to PST Example

Oracle NEW_TIME Function can convert the time from one time zone to another.

For example, the Oracle NEW_TIME Function query below converts the GMT time to PST.

SELECT NEW_TIME(SYSDATE,'GMT','PST')
FROM DUAL;

Will return something like “11/5/2012 4:24:04 AM”, here we have converted the sysdate time from GMT to PST time zone.


Oracle NEW_TIME Function – Use with SQL TO_DATE Example

Oracle NEW_TIME Function can be used with the SQL TO_DATE Function.

For example, the Oracle NEW_TIME Function query below uses SQL TO_DATE Function to convert String to Date in GMT and then returns PST.

SELECT NEW_TIME(TO_DATE('01-JAN-2012 02:30','DD-MON-YYYY HH24:MI'),PST,GMT)
FROM DUAL;

Will return something like “1/1/2012 10:30:00 AM” because we have converted the Pacific Standard Time to Greenwich Mean Time.


Filed Under: function Tagged With: how to use NEW_TIME function in oracle database query, how to use NEW_TIME function in oracle plsql, how to use NEW_TIME function in oracle sql, NEWTIMEPLSQL, NEW_TIME function in oracle plsql, NEW_TIME function in oracle sql, syntax and example of NEW_TIME function in oracle database query, syntax and example of NEW_TIME function in oracle plsql, syntax and example of NEW_TIME function in oracle sql, using NEW_TIME function in oracle database query, using NEW_TIME function in oracle plsql, using NEW_TIME function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in