This shows you the differences between two versions of the page.
— |
dev:sql:functions:new_time [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | {{page>:top_add&nofooter&noeditbtn}} | ||
+ | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
+ | {{page>:sql_bar&nofooter&noeditbtn}} | ||
+ | ====== Omnidex SQL Function: NEW_TIME ====== | ||
+ | See Also: [[ dev:sql:orafunctions:chr | CHR ]] %%|%% [[ dev:sql:orafunctions:instr | INSTR ]] %%|%%[[ dev:sql:orafunctions:new_time | NEW_TIME ]] %%|%% [[ dev:sql:orafunctions:nvl | NVL ]] %%|%% [[ dev:sql:orafunctions:substr | SUBSTR ]] %%|%% [[ dev:sql:orafunctions:sys_extract_utc | SYS_EXTRACT_UTC ]] %%|%% [[ dev:sql:orafunctions:sysdate | SYSDATE ]] %%|%% [[ dev:sql:orafunctions:systimestamp | SYSTIMESTAMP ]] %%|%% [[ dev:sql:orafunctions:to_date | TO_DATE ]] %%|%%[[ dev:sql:orafunctions:to_number | TO_NUMBER ]] | ||
+ | ===== Description ===== | ||
+ | Returns the date and time adjusted to time_zone2. The function is helpful when trying to show a time stored in the database as a standard time such as Greenish Mean Time (GMT) as a local time. | ||
+ | |||
+ | The return datatype is ORACLE DATETIME. | ||
+ | |||
+ | The NEW_TIME function uses the OMNIDEX $CONVERT function so $CONVERT will show up in the default OdxSQL headings. | ||
+ | |||
+ | <code> | ||
+ | > select new_time(sysdate, 'gmt', 'mst') from $omnidex; | ||
+ | $CONVERT($CONVERT(CURRE | ||
+ | ----------------------- | ||
+ | 06-Jul-10 | ||
+ | </code> | ||
+ | This function is only available when the CREATE ENVIRONMENT statement contains the SQL_SYNTAX ORACLE clause or the SET SQL_SYNTAX ORACLE setting is set programmatically. | ||
+ | |||
+ | ===== Syntax ===== | ||
+ | NEW_TIME(datetime, from_timezone, to_timezone) | ||
+ | |||
+ | ===== Discussion ===== | ||
+ | ==== datetime ==== | ||
+ | //datetime// is a Oracle datetime column or string. | ||
+ | ==== from_timezone and to_timezone==== | ||
+ | //from_timezone// and //to_timezone// are string literals representing an Oracle timezone. | ||
+ | |||
+ | The argument for //from_timezone// and //to_timezone// 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 ===== | ||
+ | <code> | ||
+ | > select $convert(new_time(sysdate, 'mst', 'gmt'),'odbc datetime') from $omnidex; | ||
+ | |||
+ | $CONVERT($CONVERT($CON | ||
+ | ---------------------- | ||
+ | 2010-07-06 23:00:03.00 | ||
+ | </code> | ||
+ | {{page>:bottom_add&nofooter&noeditbtn}} |