[TRAFODION-3159] Add *Syntax Descriptions and Examples* for *TO_CHAR Function* 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/963912fc Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/963912fc Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/963912fc Branch: refs/heads/master Commit: 963912fc6f1ad967c0e41745ccfeb0a69400d688 Parents: 95eaa31 Author: liu.yu <[email protected]> Authored: Fri Jul 20 18:34:14 2018 +0800 Committer: liu.yu <[email protected]> Committed: Thu Jul 26 09:41:13 2018 +0800 ---------------------------------------------------------------------- .../sql_functions_and_expressions.adoc | 807 ++++++++++++++++++- 1 file changed, 795 insertions(+), 12 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/963912fc/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 b1bed39..525ca0b 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 @@ -9066,13 +9066,220 @@ second argument describes the format of the character value. TO_CHAR(character-expression [,format-string]) ``` +[[to_char_syntax]] +=== Syntax Description of TO_CHAR + * `_character-expression_` + is an expression that gives a datetime value. * `_format-string_` + -is one of the following character string literals: +.Details of `_format-string_` +[cols="3*.^"] +|=== +^| *Supported* `_character-expression_` +^| *`_format-string_`* +^| *Description* + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'CC' +| Two-digital century. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'D' +| Day of week (1-7), where 1 is Sunday, 6 is Saturday. + +This is not configurable. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'DD' +| Day of month (01-31). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'DDD' +| Day of year (001-366). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'DY' +a| Name of day, which is a three-letter abbreviation for the day in uppercase. + + +Following values are valid: + + +* MON + + +* TUE + + +* WED + + +* THU + + +* FRI + + +* SAT + + +* SUN + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'DAY' +a| Name of day, which is full uppercase day name padded with blanks to the length of 9 characters. + + +Following values are valid: + + +* MONDAY + + +* TUESDAY + + +* WEDSDAY + + +* THURSDAY + + +* FRIDAY + + +* SATURDAY + + +* SUNDAY + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +| 'HH' +| Hour of day in 24-hour format (00-23). + +'HH' behaves the same as 'HH24'. + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +| 'HH12' +| Hour of day in 12-hour format (01-12). + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +| 'HH24' +| Hour of day in 24-hour format (00-23). + +'HH24' behaves the same as 'HH'. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'J' +| Number of days since Julian date (January 1, 4713 BC). + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +| 'MI' +| Minute (00-59). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'MM' +| Month (01-12). + + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'MON' +a| Month, which is a three-letter abbreviation for the month in uppercase. + + +Following values are valid: + + +* JAN + + +* FEB + + +* MAR + + +* APR + + +* MAY + + +* JUN + + +* JUL + + +* AUG + + +* SEP + + +* OCT + + +* NOV + + +* DEC + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'Q' +| Quarter (1-4). + +a| * `_datetime-expression_` +** TIME +** TIMESTAMP +| 'SS' +| Second (00-59). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'W' +| Week of month. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'WW' +a| Week of year. + + +* The value 1 is returned for datetime that occurs in the first 7 days of the year if the year begins on Sunday. + +Otherwise, the value 1 is returned for datetime that occurs in the partial week before the start of the first Sunday. + +* The value *53* is returned for datetime that occurs in the last full or partial week of the year. + +* The value *54* is returned for datetime that occurs in the last full or partial week of the leap year where begins on Saturday. + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'Y' +| Last digit of year (0-9). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'YY' +| Last two digits of year (00-99). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'YYY' +| Last three digits of year (000-999). + +a| * `_datetime-expression_` +** DATE +** TIMESTAMP +| 'YYYY' +| Four-digit year (0001-9999). +|=== + ++ +Besides the `_format-string_` listed above, following character string literals are also valid: ** 'YYYY-MM-DD' ** 'MM/DD/YYYY' @@ -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. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2018-11-02 23:59:59','J') FROM DUAL;; + +(EXPR) +------- +2458425 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_mi]] +==== Examples of TO_CHAR (MI) + +* This example converts the `_TIME_` value to the character value of minute. ++ +``` +SQL>SELECT TO_CHAR (TIME '01:00:02','MI') FROM DUAL; + +(EXPR) +------ +00 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of minute. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 23:59:01','MI') FROM DUAL; + +(EXPR) +------ +59 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_mm]] +==== Examples of TO_CHAR (MM) + +* This example converts the `_DATE_` value to the character value of month. ++ +``` +SQL>SELECT TO_CHAR (DATE '0001-01-01','MM') FROM DUAL; + +(EXPR) +------ +01 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of month. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 23:59:59','MM') FROM DUAL; + +(EXPR) +------ +12 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_mon]] +==== Examples of TO_CHAR (MON) + +* This example converts the `_DATE_` value to the character value of month. ++ +``` +SQL>SELECT TO_CHAR (DATE '0001-01-01','MON') FROM DUAL; + +(EXPR) +------ +JAN + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of month. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2018-11-02 23:59:59','MON') FROM DUAL; + +(EXPR) +------ +NOV + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_quarter]] +==== Examples of TO_CHAR (QUARTER) + +* This example converts the `_DATE_` value to the character value of quarter. ++ +``` +SQL>SELECT TO_CHAR (DATE '0001-01-01','Q') FROM DUAL; + +(EXPR) +------ +1 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of quarter. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2018-11-02 23:59:59','Q') FROM DUAL; + +(EXPR) +------ +4 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_ss]] +==== Examples of TO_CHAR (SS) + +* This example converts the `_TIME_` value to the character value of second. ++ +``` +SQL>SELECT TO_CHAR (TIME '01:02:00','SS') FROM DUAL; + +(EXPR) +------ +00 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of second. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2019-01-01 23:01:59','SS') FROM DUAL; + +(EXPR) +------ +59 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_w]] +==== Examples of TO_CHAR (W) + +* This example converts the `_DATE_` value to the character value of week of month. ++ +``` +SQL>SELECT TO_CHAR (DATE '2019-01-01','W') FROM DUAL; + +(EXPR) +------ +1 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of month. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2018-04-30 23:59:59','W') FROM DUAL; + +(EXPR) +------ +5 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_ww]] +==== Examples of TO_CHAR (WW) + +* This example converts the `_DATE_` value to the character value of week of year. ++ +``` +SQL>SELECT TO_CHAR (DATE '2016-01-03','WW') FROM DUAL; + +(EXPR) +------ +02 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of year. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '2000-12-31 23:59:59','WW') FROM DUAL; + +(EXPR) +------ +54 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_y]] +==== Examples of TO_CHAR (Y) + +* This example converts the `_DATE_` value to the character value of week of year (last digit). ++ +``` +SQL>SELECT TO_CHAR (DATE '1000-01-01','Y') FROM DUAL; + +(EXPR) +------ +0 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of year (last digit). ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '9999-12-31 23:59:59','Y') FROM DUAL; + +(EXPR) +------ +9 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_yy]] +==== Examples of TO_CHAR (YY) + +* This example converts the `_DATE_` value to the character value of week of year (last two digits). ++ +``` +SQL>SELECT TO_CHAR (DATE '1000-01-01','YY') FROM DUAL; + +(EXPR) +------ +00 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of year (last two digits). ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '9999-12-31 23:59:59','YY') FROM DUAL; + +(EXPR) +------ +99 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_yyy]] +==== Examples of TO_CHAR (YYY) + +* This example converts the `_DATE_` value to the character value of week of year (last three digits). ++ +``` +SQL>SELECT TO_CHAR (DATE '1000-01-01','YYY') FROM DUAL; + +(EXPR) +------ +000 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of year (last three digits). ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '9999-12-31 23:59:59','YYY') FROM DUAL; + +(EXPR) +------ +999 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_yyyy]] +==== Examples of TO_CHAR (YYYY) + +* This example converts the `_DATE_` value to the character value of week of year. ++ +``` +SQL>SELECT TO_CHAR (DATE '0001-01-01','YYYY') FROM DUAL; + +(EXPR) +------ +0001 + +--- 1 row(s) selected. +``` + +* This example converts the `_TIMESTAMP_` value to the character value of week of year. ++ +``` +SQL>SELECT TO_CHAR (TIMESTAMP '9999-12-31 23:59:59','YYYY') FROM DUAL; + +(EXPR) +------ +9999 + +--- 1 row(s) selected. +``` + +[[examples_of_to_char_other]] +==== Examples of TO_CHAR (Other) + * This function returns the character value '01MAR2016': + ```
