You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by hz...@apache.org on 2018/03/15 18:02:05 UTC
[1/2] trafodion git commit: Add SYSDATE in *Trafodion SQL Reference
Manual*
Repository: trafodion
Updated Branches:
refs/heads/master cb5ebb966 -> 21735bca2
Add SYSDATE in *Trafodion SQL Reference Manual*
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/c9ba5fb3
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/c9ba5fb3
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/c9ba5fb3
Branch: refs/heads/master
Commit: c9ba5fb31b6bd07d234d54e69fface725eb92aae
Parents: b19868a
Author: liu.yu <qw...@hotmail.com>
Authored: Thu Mar 15 16:22:45 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Thu Mar 15 16:22:45 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 147 +++++++++++++++++++
1 file changed, 147 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/c9ba5fb3/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index c72feeb..151aead 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -7953,6 +7953,153 @@ SELECT SUM (price * qty_available) FROM sales.parts;
```
<<<
+[[sysdate_function]]
+== SYSDATE Function
+
+The SYSDATE function, which is equivalent to the `CURRENT_DATE` function, retrieves the current date of the server rather than the session.
+
+The returned value is `DATE` and the default format is `YYYY-MM-DD`.
+
+For example, if you execute a query on your local machine located in Shanghai on 2018-03-14 06:00:00 (UTC+8) against a database server located in Berlin on 2018-03-13 23:00:00 (UTC+1), the result of `SELECT SYSDATE FROM DUAL;` is 2018-03-13 rather than 2018-03-14.
+
+```
+SYSDATE
+```
+
+[[examples_of_sysdate]]
+=== Examples of SYSDATE
+
+* This example returns the current date.
+
++
+```
+SQL>SELECT SYSDATE FROM DUAL;
+
+(EXPR)
+----------
+2018-03-15
+
+--- 1 row(s) selected.
+```
+
+* This example returns the date of yesterday, today and tomorrow.
+
++
+```
+SQL>SELECT SYSDATE -1 AS yesterday,
+SYSDATE AS today,
+SYSDATE +1 AS tomorrow FROM DUAL;
+
+YESTERDAY TODAY TOMORROW
+---------- ---------- ----------
+2018-03-14 2018-03-15 2018-03-16
+
+--- 1 row(s) selected.
+```
+
+* The following examples show that the values of `SYSDATE` can be converted to character values.
+
++
+```
+SQL>SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY') FROM DUAL;
+
+(EXPR)
+-----------
+15-MAR-2018
+
+--- 1 row(s) selected.
+```
+
++
+```
+SQL>SELECT TO_CHAR (SYSDATE,'HH:MI:SS') FROM DUAL;
+
+*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time. [2018-03-15 11:49:22]
+```
+
++
+```
+SQL>SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') FROM DUAL;
+
+(EXPR)
+-------------------
+03/15/2018 00:00:00
+
+--- 1 row(s) selected.
+```
+
+* This example converts days to minutes using `SYSDATE`.
++
+```
+SQL>SELECT (SYSDATE-(SYSDATE-7))*1440 FROM DUAL;
+
+(EXPR)
+-----------------
+ 10080
+
+--- 1 row(s) selected.
+```
+
+* This example demonstrates how the SYSDATE function works in SQL statement.
++
+Suppose that we have the following table:
+
++
+```
+SQL>SELECT * FROM orders;
+
+ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
+-------- ---------- ---------- -------- -------
+ 100210 2018-03-02 2018-04-10 220 127
+ 100250 2018-01-23 2018-06-16 220 123
+ 101220 2018-02-21 2018-12-15 221 156
+ 200300 2018-02-06 2018-07-15 222 126
+ 200320 2018-03-08 2018-07-20 223 121
+ 200490 2018-02-19 2018-11-01 226 123
+ 300350 2018-03-03 2018-08-10 231 123
+ 300380 2018-01-19 2018-08-15 226 156
+ 400410 2018-01-27 2018-09-14 227 154
+ 500450 2018-03-12 2018-09-16 220 124
+ 600480 2018-02-12 2018-10-14 226 123
+ 700510 2018-02-01 2018-10-16 220 143
+ 800660 2018-01-09 2018-11-01 229 100
+
+--- 13 row(s) selected.
+```
+
++
+The SYSDATE is 2018-03-15.
++
+```
+SQL>SELECT SYSDATE FROM DUAL;
+
+(EXPR)
+----------
+2018-03-15
+
+--- 1 row(s) selected.
+```
+
++
+This statement returns qualified rows using `SYSDATE` function.
++
+```
+SQL>SELECT * FROM orders
+WHERE DAY(deliv_date) = DAY(sysdate+1)
+AND ordernum <>100210
+AND salesrep=220
+ORDER BY order_date DESC;
+
+ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
+-------- ---------- ---------- -------- -------
+ 500450 2018-03-12 2018-09-16 220 124
+ 700510 2018-02-01 2018-10-16 220 143
+ 100250 2018-01-23 2018-06-16 220 123
+
+--- 3 row(s) selected.
+```
+
+<<<
[[tan_function]]
== TAN Function
[2/2] trafodion git commit: Merge [TRAFODION-2993] Add *SYSDATE* in
*Trafodion SQL Reference Manual* PR-1479
Posted by hz...@apache.org.
Merge [TRAFODION-2993] Add *SYSDATE* in *Trafodion SQL Reference Manual* PR-1479
Conflicts:
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/21735bca
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/21735bca
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/21735bca
Branch: refs/heads/master
Commit: 21735bca28980a500af11da157d39141e7ae4be7
Parents: cb5ebb9 c9ba5fb
Author: Hans Zeller <hz...@apache.org>
Authored: Thu Mar 15 17:54:25 2018 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Thu Mar 15 17:54:25 2018 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 148 +++++++++++++++++++
1 file changed, 148 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/21735bca/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --cc docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 9258b76,151aead..83f8313
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@@ -7953,63 -7953,153 +7953,211 @@@ SELECT SUM (price * qty_available) FRO
```
<<<
+ [[sysdate_function]]
+ == SYSDATE Function
+
+ The SYSDATE function, which is equivalent to the `CURRENT_DATE` function, retrieves the current date of the server rather than the session.
+
+ The returned value is `DATE` and the default format is `YYYY-MM-DD`.
+
+ For example, if you execute a query on your local machine located in Shanghai on 2018-03-14 06:00:00 (UTC+8) against a database server located in Berlin on 2018-03-13 23:00:00 (UTC+1), the result of `SELECT SYSDATE FROM DUAL;` is 2018-03-13 rather than 2018-03-14.
+
+ ```
+ SYSDATE
+ ```
+
+ [[examples_of_sysdate]]
+ === Examples of SYSDATE
+
+ * This example returns the current date.
+
+ +
+ ```
+ SQL>SELECT SYSDATE FROM DUAL;
+
+ (EXPR)
+ ----------
+ 2018-03-15
+
+ --- 1 row(s) selected.
+ ```
+
+ * This example returns the date of yesterday, today and tomorrow.
+
+ +
+ ```
+ SQL>SELECT SYSDATE -1 AS yesterday,
+ SYSDATE AS today,
+ SYSDATE +1 AS tomorrow FROM DUAL;
+
+ YESTERDAY TODAY TOMORROW
+ ---------- ---------- ----------
+ 2018-03-14 2018-03-15 2018-03-16
+
+ --- 1 row(s) selected.
+ ```
+
+ * The following examples show that the values of `SYSDATE` can be converted to character values.
+
+ +
+ ```
+ SQL>SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY') FROM DUAL;
+
+ (EXPR)
+ -----------
+ 15-MAR-2018
+
+ --- 1 row(s) selected.
+ ```
+
+ +
+ ```
+ SQL>SELECT TO_CHAR (SYSDATE,'HH:MI:SS') FROM DUAL;
+
+ *** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time. [2018-03-15 11:49:22]
+ ```
+
+ +
+ ```
+ SQL>SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') FROM DUAL;
+
+ (EXPR)
+ -------------------
+ 03/15/2018 00:00:00
+
+ --- 1 row(s) selected.
+ ```
+
+ * This example converts days to minutes using `SYSDATE`.
+ +
+ ```
+ SQL>SELECT (SYSDATE-(SYSDATE-7))*1440 FROM DUAL;
+
+ (EXPR)
+ -----------------
+ 10080
+
+ --- 1 row(s) selected.
+ ```
+
+ * This example demonstrates how the SYSDATE function works in SQL statement.
+ +
+ Suppose that we have the following table:
+
+ +
+ ```
+ SQL>SELECT * FROM orders;
+
+ ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
+ -------- ---------- ---------- -------- -------
+ 100210 2018-03-02 2018-04-10 220 127
+ 100250 2018-01-23 2018-06-16 220 123
+ 101220 2018-02-21 2018-12-15 221 156
+ 200300 2018-02-06 2018-07-15 222 126
+ 200320 2018-03-08 2018-07-20 223 121
+ 200490 2018-02-19 2018-11-01 226 123
+ 300350 2018-03-03 2018-08-10 231 123
+ 300380 2018-01-19 2018-08-15 226 156
+ 400410 2018-01-27 2018-09-14 227 154
+ 500450 2018-03-12 2018-09-16 220 124
+ 600480 2018-02-12 2018-10-14 226 123
+ 700510 2018-02-01 2018-10-16 220 143
+ 800660 2018-01-09 2018-11-01 229 100
+
+ --- 13 row(s) selected.
+ ```
+
+ +
+ The SYSDATE is 2018-03-15.
+ +
+ ```
+ SQL>SELECT SYSDATE FROM DUAL;
+
+ (EXPR)
+ ----------
+ 2018-03-15
+
+ --- 1 row(s) selected.
+ ```
+
+ +
+ This statement returns qualified rows using `SYSDATE` function.
+ +
+ ```
+ SQL>SELECT * FROM orders
+ WHERE DAY(deliv_date) = DAY(sysdate+1)
+ AND ordernum <>100210
+ AND salesrep=220
+ ORDER BY order_date DESC;
+
+ ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
+ -------- ---------- ---------- -------- -------
+ 500450 2018-03-12 2018-09-16 220 124
+ 700510 2018-02-01 2018-10-16 220 143
+ 100250 2018-01-23 2018-06-16 220 123
+
+ --- 3 row(s) selected.
+ ```
+
+ <<<
+[[systimestamp_function]]
+== SYSTIMESTAMP Function
+
+The SYSTIMESTAMP function, which is equivalent to the `CURRENT_TIMESTAMP` function, provides much high granularity than the `SYSDATE` function and retrieves the current date and time (including fractional seconds with six-digit precision) of the server rather than the session.
+
+The returned value is `TIMESTAMP` and the default format is `YYYY-MM-DD HH:MM:SS.FFFFFF`.
+
+For example, if you execute a query on your local machine located in Shanghai on 2018-03-14 06:00:00 (UTC+8) against a database server located in Berlin on 2018-03-13 23:00:00 (UTC+1), the result of `SELECT SYSTIMESTAMP FROM DUAL;` is 2018-03-13 23:00:00 rather than 2018-03-14 06:00:00.
+
+```
+SYSTIMESTAMP
+```
+
+[[examples_of_systimestamp]]
+=== Examples of SYSTIMESTAMP
+
+* This example calculates the date and time of anniversary using SYSTIMESTAMP function.
+
++
+```
+SELECT SYSTIMESTAMP AS today, SYSTIMESTAMP + INTERVAL '12' MONTH AS Annisversary FROM DUAL;
+
+TODAY ANNISVERSARY
+-------------------------- --------------------------
+2018-03-15 11:19:42.400382 2019-03-15 11:19:42.400382
+
+--- 1 row(s) selected.
+```
+
+* This example demonstrates how to insert the value of SYSTIMESTAMP into a column.
+
++
+```
+SQL>CREATE TABLE test1 (C1 TIMESTAMP, C2 VARCHAR(40));
+
+--- SQL operation complete.
+```
+
++
+```
+SQL>INSERT INTO test1 VALUES (SYSTIMESTAMP, 'This is the time that I insert values');
+
+--- 1 row(s) inserted.
+```
+
++
+```
+SQL>SELECT * FROM test1;
+
+C1 C2
+-------------------------- ----------------------------------------
+2018-03-15 11:33:32.091057 This is the time that I insert values
+
+--- 1 row(s) selected.
+```
+
++
+<<<
[[tan_function]]
== TAN Function