Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
NEW_TIME
returns the date and time in time zone timezone2
when date and time in time zone timezone1
are date
. Before using this function, you must set the NLS_DATE_FORMAT
parameter to display 24-hour time. The return type is always DATE
, regardless of the datatype of date
.
Note: This function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining theFROM_TZ function and the datetime expression. See FROM_TZ and the example for "Datetime Expressions". |
The arguments timezone1
and timezone2
can be any of these text strings:
AST, ADT: Atlantic Standard or Daylight Time
BST, BDT: Bering Standard or Daylight Time
CST, CDT: Central Standard or Daylight Time
EST, EDT: Eastern Standard or Daylight Time
GMT: Greenwich Mean Time
HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
MST, MDT: Mountain Standard or Daylight Time
NST: Newfoundland Standard Time
PST, PDT: Pacific Standard or Daylight Time
YST, YDT: Yukon Standard or Daylight Time
Examples
The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL; New Date and Time -------------------- 09-NOV-1999 21:23:45