Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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)

Parameters

this_zone

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.

new_zone

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 8-1, "Time Zones".

Table 8-1 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

Example 8-33 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

Example 8-34 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')