Oracle NEW_TIME function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Generates a new date based on a given date and time zone and an input time zone in Oracle

The NEW_TIME() function is used to convert a date from timezone1 to a date in timezone2.

Syntax:

NEW_TIME(date, timezone1, timezone2)

Parameters:

Name	Description
date	Specified date.
timezone1	Original timezone.
timezone2	New timezone.

The arguments timezone1 and timezone2 can be any of these text strings:

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

Pictorial Presentation

Examples: Oracle NEW_TIME() function

The following example returns a Central Standard Time, given the Pacific Standard time equivalent :

SQL> ALTER SESSION SET NLS_DATE_FORMAT =
  2     'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT NEW_TIME(TO_DATE(
  2     '03-04-2015 03:25:45', 'MM-DD-YY HH24:MI:SS'),
  3     'CST', 'PST') "New Date and Time" FROM DUAL;

Sample Output:

New Date and Time
--------------------
  04-MAR-2015 01:25:45

Previous: MONTHS_BETWEEN
Next: NEXT_DAY