Oracle NEW_TIME Function

0 Flares Filament.io 0 Flares ×

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.


Tagged , , , , , , , , , , , . Bookmark the permalink.