NEW_TIME |
The NEW_TIME function converts a date and time from one time zone to another.
Return Value
DATETIME
Syntax
NEW_TIME(datetime-exp this_zone new_zone)
Arguments
A text expression that indicates the time zone from which you want to convert datetime-exp. It must be a valid time zone, as listed in the following table.
A text expression that indicates the time zone into which you want to convert datetime-exp. It is the time zone of the return value. It must be a valid time zone, as listed in Table: Time Zones.
| 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 |
Examples
Using the Current Time of day
The SYSDATE function returns the current date and time to the NEW_TIME function.
SHOW new_time(SYSDATE 'EST' 'PST')
When the date and time in Eastern Standard Time are October 20, 2000, at 1:20 A.M., then the date in Pacific Standard Time, which is three hours earlier, is October 19, 2000. Because SYSDATE uses the format specified by NLS_DATE_FORMAT, which by default only shows the date, the time is not displayed.
19-OCT-00
Specifying the Time of day
In the following example, the TO_DATE function converts a text string to a valid date and time. The TO_CHAR function includes a date format that temporarily overrides the date format specified by the NLS_DATE_FORMAT option.
SHOW TO_CHAR(NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), -
'HST' 'PST') 'MM-DD-YY HH24:MI:SS')
This statement converts November 27 at 10:15 P.M. (22:15:00) Alaska-Hawaii Standard Time to November 28 at 12:15 A.M. (00:15:00) Pacific Standard Time. The date format specified in the TO_CHAR function allows the time to be displayed along with the date.
11-28-00 00:15:00
Alternatively, you can change the value of NLS_DATE_FORMAT.
NLS_DATE_FORMAT = 'MM-DD-YY HH24:MI:SS'
Then this statement produces the same result, without requiring the use of TO_CHAR.
SHOW NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), -
'HST' 'PST')