This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:orafunctions:new_time [2010/07/07 20:21] tdo |
— (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 ====== | ||
| - | [[ 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}} | ||