NEW_TIME date function in oracle

By Santhosh N

This explains how to convert the date and time from one time zone to other and return the equivalent time to desired time zone.

Oracle has a Date function by the name, NEW_TIME which returns a date and time information in the time zone to the date and time information in the desired time zone.

Syntax: NEW_TIME(date, TimeZone 1, TimeZone2)

Here date is the date and time of the time that needs to be shown in the desired time zone, TimeZone1 is the source time zone from which conversion needs to be done and TimeZone2 is the target time zone to which the date needs to converted and returned.

Ex1: SELECT NEW_TIME(TO_DATE('2010/05/25 8:45', 'yyyy/mm/dd HH24:MI'), 'PST', 'CST') FROM dual;
This returns “5/25/2010 10:45:00 AM”, after converting date from PST to CST.

Note: The supported time zones are as follows:

TImeZone 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

Related FAQs

This explains the difference between two oracle date functions namely LOCALTIMESTAMP and CURRENT_TIMESTAMP.
This explains how to get the current date and time in the time zone of the current SQL session which is set by the Alter session command.
This explains how to convert the timestamp value to the timestamp with time zone in oracle.
This explains how to get the Database Time zone information that is set to the current session.
This explains how to get the current date and time along with the time zone that is set for the session.
This explains how to get the current date along with the time that is set for the session.
NEW_TIME date function in oracle  (3285 Views)