[
https://issues.apache.org/jira/browse/TRAFODION-3159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16554632#comment-16554632
]
ASF GitHub Bot commented on TRAFODION-3159:
-------------------------------------------
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1656#discussion_r204860494
--- Diff:
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -9098,30 +9305,606 @@ is one of the following character string literals:
** 'MM/DD/YYYY HH24:MI:SS'
** 'DD-MON-YYYY HH:MI:SS'
** 'MONTH DD, YYYY, HH:MI'
-** 'DD.MM.YYYY HH24.MI.SS'
-
-Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM
refers to a two-digit month. MON refers to
-a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY',
'JUN', 'JUL', 'AUG', 'SEP',
-'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. In the
output value, the month abbreviation or month
-will appear in upper case.
+** 'DD.MM.YYYY HH24.MI.SS'
-HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit
minutes field. SS refers to a 2-digit seconds field.
++
+Here:
-If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
+*** YYYY refers to a four-digit year.
+*** YY refers to a two-digit year.
+*** MM refers to a two-digit month.
+*** MON refers to a three-letter abbreviation for month ('JAN', 'FEB',
'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
+'OCT', 'NOV' or 'DEC').
+*** MONTH refers to the month spelled out. In the output value, the month
abbreviation or month
+will appear in upper case.
+*** HH and HH24 refer to a two-digit hour field.
+*** MI refers to a two-digit minutes field.
+*** SS refers to a two-digit seconds field.
+*** If the `_format-string_` argument is omitted, 'YYYY-MM-DD' is used as
the
default for date and timestamp values, and 'HH:MI:SS' is used for time
values.
[[considerations_for_to_char]]
=== Considerations for TO_CHAR
-If the _format-string_ contains only hour, minute or seconds fields, the
input data type must be time or timestamp.
+If the `_format-string_` contains only hour, minute or seconds fields, the
input data type must be time or timestamp.
-If the _format-string_ contains only year, month or day fields, the input
data type must be date or timestamp.
+If the `_format-string_` contains only year, month or day fields, the
input data type must be date or timestamp.
-If the _format-string_ contains all fields, and the input data type is
date, the hour, minute and second fields in the result will be filled with
zeroes.
+If the `_format-string_` contains all fields, and the input data type is
date, the hour, minute and second fields in the result will be filled with
zeroes.
[[examples_of_to_char]]
=== Examples of TO_CHAR
+[[examples_of_to_char_cc]]
+==== Examples of TO_CHAR (CC)
+
+* This example converts the `_DATE_` value to the character value of
century.
++
+```
+SQL>SELECT TO_CHAR (DATE '0001-11-02','CC') FROM DUAL;
+
+(EXPR)
+------
+01
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
century.
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '9899-12-31 23:59:59','CC') FROM DUAL;
+
+(EXPR)
+------
+99
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_d]]
+==== Examples of TO_CHAR (D)
+
+* This example converts the `_DATE_` value to the character value of day
of week.
++
+```
+SQL>SELECT TO_CHAR (DATE '2018-01-01','D') FROM DUAL;
+
+(EXPR)
+------
+2
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
day of week.
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2018-04-01 23:59:59','D') FROM DUAL;
+
+(EXPR)
+------
+1
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_dd]]
+==== Examples of TO_CHAR (DD)
+
+* This example converts the `_DATE_` value to the character value of day
of month.
++
+```
+SQL>SELECT TO_CHAR (DATE '2018-01-01','DD') FROM DUAL;
+
+(EXPR)
+------
+01
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
day of month.
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2018-12-31 23:59:59','DD') FROM DUAL;
+
+(EXPR)
+------
+31
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_ddd]]
+==== Examples of TO_CHAR (DDD)
+
+* This example converts the `_DATE_` value to the character value of day
of year.
++
+```
+SQL>SELECT TO_CHAR (DATE '2018-01-01','DDD') FROM DUAL;
+
+(EXPR)
+------
+001
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
day of year.
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2020-12-31 23:59:59','DDD') FROM DUAL;
+
+(EXPR)
+------
+366
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_dy]]
+==== Examples of TO_CHAR (DY)
+
+* This example converts the `_DATE_` value to the character value of name
of day (abbreviation).
++
+```
+SQL>SELECT TO_CHAR (DATE '2018-12-31','DY') FROM DUAL;
+
+(EXPR)
+------
+MON
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
name of day (abbreviation).
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2019-02-14 23:59:59','DY') FROM DUAL;
+
+(EXPR)
+------
+THU
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_day]]
+==== Examples of TO_CHAR (DAY)
+
+* This example converts the `_DATE_` value to the character value of name
of day.
++
+```
+SQL>SELECT TO_CHAR (DATE '2019-05-12','DAY') FROM DUAL;
+
+(EXPR)
+---------
+SUNDAY
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
name of day.
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2019-12-24 23:59:59','DAY') FROM DUAL;
+
+(EXPR)
+---------
+TUESDAY
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_hh]]
+==== Examples of TO_CHAR (HH)
+
+* This example converts the `_TIME_` value to the character value of hour
(24-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIME '00:00:01','HH') FROM DUAL;
+
+(EXPR)
+------
+00
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
hour (24-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 23:59:59','HH') FROM DUAL;
+
+(EXPR)
+------
+23
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_hh12]]
+==== Examples of TO_CHAR (HH12)
+
+* This example converts the `_TIME_` value to the character value of hour
(12-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIME '01:00:00','HH12') FROM DUAL;
+
+(EXPR)
+------
+01
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
hour (12-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 12:59:59','HH12') FROM DUAL;
+
+(EXPR)
+------
+12
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_hh24]]
+==== Examples of TO_CHAR (HH24)
+
+* This example converts the `_TIME_` value to the character value of hour
(24-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIME '00:00:01','HH24') FROM DUAL;
+
+(EXPR)
+------
+00
+
+--- 1 row(s) selected.
+```
+
+* This example converts the `_TIMESTAMP_` value to the character value of
hour (24-hour format).
++
+```
+SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 23:59:59','HH24') FROM DUAL;
+
+(EXPR)
+------
+23
+
+--- 1 row(s) selected.
+```
+
+[[examples_of_to_char_j]]
+==== Examples of TO_CHAR (J)
+
+* This example caculates the number of days since Julian date and converts
the `_DATE_` value to the character value.
++
+```
+SQL>SELECT TO_CHAR (DATE '0001-01-01','J') FROM DUAL;
+
+(EXPR)
+-------
+1721426
+
+--- 1 row(s) selected.
+```
+
+* This example caculates the number of days since Julian date and converts
the `_TIMESTAMP_` value to the character value.
--- End diff --
Same comment as above
> Add *Syntax Descriptions and Examples* for *TO_CHAR Function* in *Trafodion
> SQL Reference Manual*
> -------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-3159
> URL: https://issues.apache.org/jira/browse/TRAFODION-3159
> Project: Apache Trafodion
> Issue Type: Documentation
> Reporter: Liu Yu
> Assignee: Liu Yu
> Priority: Major
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
