OMDG-Examples

DML

//
// Demonstration of how to use the various MaxDB functions
//
//   -> server supplied functions
SELECT TIMESTAMP FROM dual
//
SELECT YEAR(TIMESTAMP) FROM dual
//
SELECT MONTH(TIMESTAMP) FROM dual
//
SELECT MONTHNAME(TIMESTAMP) FROM dual
//
SELECT WEEKOFYEAR(TIMESTAMP),WEEK(TIMESTAMP) FROM dual
//
SELECT DAYOFYEAR(TIMESTAMP) FROM dual
//
SELECT DAYOFMONTH(TIMESTAMP) FROM dual
//
SELECT DAYOFWEEK(TIMESTAMP) FROM dual
//
SELECT DAY(TIMESTAMP) FROM dual
//
SELECT DAYNAME(TIMESTAMP) FROM dual
//
SELECT DATE(TIMESTAMP),TIMESTAMP,UTCDATE FROM dual
//
SELECT TIME(TIMESTAMP) FROM dual
//
SELECT HOUR(TIME(TIMESTAMP))   AS "Hour"
     , MINUTE(TIME(TIMESTAMP)) AS "Minute"
     , SECOND(TIME(TIMESTAMP)) AS "Second"  
     , MICROSECOND(TIMESTAMP)  AS "Microsecond"  
  FROM dual
//
SELECT TIMEDIFF(TIMESTAMP,UTCDATE) AS "TimeDif_2_ECT/UTC" FROM dual
//
SELECT HOUR(TIMEDIFF(TIMESTAMP,UTCDATE)) AS "TimeDif_2_ECT/UTC" FROM dual
//
SELECT UTCDATE,TIMEZONE FROM dual
//
SELECT NUM(DATE)-2 FROM dual
//
SELECT SUBDATE(DATE,2) FROM dual
//
SELECT CHAR(DATE,INTERNAL)      AS "Character Format"
      ,NUM(CHAR(DATE,INTERNAL)) AS "Numeric   Format"
      ,CHAR(DATE,EUR)           AS "European  Format"
      ,CHAR(DATE,USA)           AS "US        Format"
      ,CHAR(DATE,ISO)           AS "ISO       Format"
  FROM DUAL
//

Result

> SELECT TIMESTAMP FROM dual
> | EXPRESSION1                |
> | -------------------------- |
> | 2008-02-17 10:54:47.359000 |
> SELECT YEAR(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |               2008 |
> SELECT MONTH(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |                  2 |
> SELECT MONTHNAME(TIMESTAMP) FROM dual
> | EXPRESSION1  |
> | ------------ |
> | February     |
> SELECT WEEKOFYEAR(TIMESTAMP),WEEK(TIMESTAMP) FROM dual
> | EXPRESSION1        | EXPRESSION2        |
> | ------------------ | ------------------ |
> |                  7 |                  7 |
> SELECT DAYOFYEAR(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |                 48 |
> SELECT DAYOFMONTH(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |                 17 |
> SELECT DAYOFWEEK(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |                  7 |
> SELECT DAY(TIMESTAMP) FROM dual
> | EXPRESSION1        |
> | ------------------ |
> |                 17 |
> SELECT DAYNAME(TIMESTAMP) FROM dual
> | EXPRESSION1  |
> | ------------ |
> | Sunday       |
> SELECT DATE(TIMESTAMP),TIMESTAMP,UTCDATE FROM dual
> | EXPRESSION1 | EXPRESSION2                | EXPRESSION3                |
> | ----------- | -------------------------- | -------------------------- |
> | 2008-02-17  | 2008-02-17 10:54:47.359000 | 2008-02-17 09:54:47.359000 |
> SELECT TIME(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ----------- |
> | 10:54:47    |
> SELECT HOUR(TIME(TIMESTAMP))   AS "Hour"      , MINUTE(TIME(TIMESTAMP)) AS "Minute"      , SECOND(TIME(TIMESTAMP)) AS "Second"      , MICROSECOND(TIMESTAMP)  AS "Microsecond"   FROM dual
> | Hour     | Minute      | Second      | Microsecond        |
> | -------- | ----------- | ----------- | ------------------ |
> |       10 |          54 |          47 |             359000 |
> SELECT TIMEDIFF(TIMESTAMP,UTCDATE) AS "TimeDif_2_ECT/UTC" FROM dual
> | TimeDif_2_ECT/UTC |
> | ----------------- |
> | 01:00:00          |
> SELECT HOUR(TIMEDIFF(TIMESTAMP,UTCDATE)) AS "TimeDif_2_ECT/UTC" FROM dual
> | TimeDif_2_ECT/UTC          |
> | -------------------------- |
> |                          1 |
> SELECT UTCDATE,TIMEZONE FROM dual
> | EXPRESSION1                | EXPRESSION2        |
> | -------------------------- | ------------------ |
> | 2008-02-17 09:54:47.359000 |              10000 |
> SELECT NUM(DATE)-2 FROM dual
> | EXPRESSION1                                    |
> | ---------------------------------------------- |
> |                                       20080215 |
> SELECT SUBDATE(DATE,2) FROM dual
> | EXPRESSION1 |
> | ----------- |
> | 2008-02-15  |