Convert Dates to Formatted Date Strings
This statement uses TO_CHAR to convert $HOROLOG date integers or full $HOROLOG string values to formatted date strings or date and time strings:
SELECT
TO_CHAR(66256,'YYYY-MM-DD') AS Date2FormattedDate,
TO_CHAR(66256,'YYYY-MM-DD HH24:MI:SS') AS Date2FormattedDateTime,
TO_CHAR('66256,50278','YYYY-MM-DD') AS DateTime2FormattedDate,
TO_CHAR('66256,50278','YYYY-MM-DD HH24:MI:SS') AS DateTime2FormattedDateTime
In this statement, each TO_CHAR call takes a date integer and returns a date string formatted according to the format string argument:
SELECT
TO_CHAR(66256,'MM/DD/YYYY'), /* returns 02/22/2018 */
TO_CHAR(66256,'DAY MONTH DD, YYYY') /* returns Thursday February 22, 2018 */
This statement converts a date integer to a formatted date string. Invalid format characters are passed through to the output string as literals. It returns the string The date 05/27/2022 should be noted.
SELECT TO_CHAR(66256,'The date MM/DD/YYYY should be noted')
This statement converts date expressions to the day of the year, defined as the number of days elapsed since January 1 of the specified year. To use this syntax, the date expression must be in $HOROLOG format. The time value in the second TO_CHAR call is ignored. In the two TO_CHAR calls, the day-of-year format element (DDD) and the year elements (YYYY and YY) appear in a different order.
SELECT
TO_CHAR('66235','DDD days into YYYY'),
TO_CHAR('66235,12345','Year YY: DDD days elapsed')
In this statement, TO_CHAR returns an incorrect date value, because it interprets the separators as minus signs. Therefore, it evaluates the expression as 2022 – 5 – 2 = 2015, which in $HOROLOG integer format corresponds to the date 1846-07–08.
SELECT TO_CHAR(2022-05-02,'YYYY-MM-DD') -- Incorrect usage
Convert Times to Formatted Time Strings
This statement causes '66256' to be interpreted as the time value 06:24:16 PM.
SELECT TO_CHAR('66256','HH12:MI:SS PM')
This statement converts the time portions of two Logical timestamps to formatted time strings. Because format does not support fractional seconds, the fractional seconds in expression are truncated.
SELECT TO_CHAR(SYSDATE,'HH12:MI:SS PM'),
TO_CHAR(CURRENT_TIMESTAMP(6),'HH12:MI:SS PM')
Convert Numbers to Formatted Numeric Strings
This statement converts the number 1000 to strings with varying numbers of digit format codes.
-
In the first conversion, the number has more digits than specified digit format codes. TO_CHAR returns pound symbols equal to the number of digits in the number.
-
In the second conversion, the number has the same number of digits as specified digit format codes. TO_CHAR returns the number in character string form. Because the number is an unsigned positive integer, TO_CHAR prepends a leading zero to the numeric string.
-
In the third conversion, the number has fewer digits than specified digit format codes. TO_CHAR returns the number in character string form and prepends two leading zeros: one because the number is an unsigned positive integer one for the extra digit format code.
SELECT
TO_CHAR(1000,'999'), -- '####'
TO_CHAR(1000,'9999'), -- ' 1000'
TO_CHAR(1000,'99999') -- ' 1000'
The statement shows the use of separator characters:
-
The first conversion returns the string: ' 1,000.00'.
-
The second conversion might return the same value, but the separator characters displayed depend on the locale setting.
SELECT
TO_CHAR(1000,'9,999.99'),
TO_CHAR(1000,'9G999D99')
This statement shows the use of positive and negative signs. The leading space appears only before a positive number with no sign formatting. No leading space appears before a negative number or any signed number, regardless of the placement of the sign.
SELECT
TO_CHAR(10,'99.99'), -- ' 10.00'
TO_CHAR(-10,'99.99'), -- '-10.00'
TO_CHAR(10,'S99.99'), -- '+10.00'
TO_CHAR(-10,'S99.99'), -- '-10.00'
TO_CHAR(10,'99.99S'), -- '10.00+'
TO_CHAR(-10,'99.99S') -- '10.00-'
This statement shows the use of the "FM" format to override the default leading blank for unsigned positive numbers:
SELECT
TO_CHAR(12345678.90,'99,999,999.99'), -- ' 12,345,678,90'
TO_CHAR(12345678.90,'FM99,999,999.99') -- '12,345,678,90'
This statement shows the use of the leading dollar sign. The dollar sign is always preceded either by a sign or by a blank character.
SELECT
TO_CHAR(1234567890,'$9G999G999G999'), -- ' $1,234,567,890'
TO_CHAR(1234567890,'S$9G999G999G999'), -- '+$1,234,567,890'
TO_CHAR(12345678.90,'$99G999G999D99') -- ' $1,234,567,8.90'
The statement shows what happens when the format argument contains fewer decimal (fractional) digits than the input numeric expression. The returned numbers are rounded to 1234567.5 and 1234568, respectively.
SELECT
TO_CHAR(1234567.4999,'9999999.9'),
TO_CHAR(1234567.91,'9999999')