Formatting functions of POLARDB compatible with Oracle provide a powerful set of tools for converting various data types (date/time, integer, floating point, and numeric) into formatted strings. These functions can also convert formatted strings into specific data types. The following table describes these formatting functions. These functions follow a common calling convention. The first argument is the value to be formatted and the second argument is a string template that defines the output or input format.

Table 1. Formatting functions
Function Return type Description Example Result
TO CHAR(DATE [, format ]) VARCHAR2 Converts a date/time to a string in the format specified by the format argument. If you omit the format argument, the function returns a string in the default format (DD-MON- YY). TO CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM') 07/25/2007 09:43:02 AM
TO CHAR(INTEGER [, format ]) VARCHAR2 Converts an integer to a string in the format specified by the format argument. TO CHAR(2412, '999,999S') 2,412+
TO CHAR(NUMBER [, format ]) VARCHAR2 Converts a decimal number to a string in the format specified by the format argument. TO CHAR(10125.35, '999,999.99') 10,125.35
TO CHAR(DOUBLE PRECISION, format) VARCHAR2 Converts a floating-point number to a string in the format specified by the format argument. TO CHAR(CAST(123.5282 AS REAL), '999.99') 123.53
TO DATE(string [, format ]) DATE Converts a date formatted string to a DATE data type. TO DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS') 04-JUL-07 13:39:10
TO NUMBER(string [, format ]) NUMBER Converts a number formatted string to a NUMBER data type. TO NUMBER('2,412-', '999,999S') -2412
TO TIMESTAMP(string, format) TIMESTAMP Converts a timestamp formatted string to a TIMESTAMP data type. TO TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm') 05-DEC-00 20:30:25

In an output template string for the TO_CHAR function, some specific patterns are recognized and replaced with appropriately-formatted data from the value to be formatted. Any text that is not a template pattern is an exact copy. Similarly, in an input template string (for any function but TO_CHAR), template patterns identify the parts of the input data string to be looked at and the values to be found there.

The following table lists the available template patterns for formatting date values by using the TO_CHAR and TO_DATE functions.

Table 2. Template date/time format patterns
Pattern Description
HH Hour of day (01-12)
HH12 Hour of day (01-12)
HH24 Hour of day (00-23)
MI Minute (00-59)
SS Second (00-59)
SSSSS Seconds past midnight (0-86399)
AM or A.M. or PM or P.M. Meridian indicator (uppercase)
am or a.m. or pm or p.m. Meridian indicator (lowercase)
Y,YYY Year (4 and more digits) with comma
YEAR Year (spelled out)
SYEAR Year (spelled out) (BC dates prefixed by a minus sign)
YYYY Year (4 and more digits)
SYYYY Year (4 and more digits) (BC dates prefixed by a minus sign)
YYY Last 3 digits of year
YY Last 2 digits of year
Y Last digit of year
IYYY ISO year (4 and more digits)
IYY Last 3 digits of ISO year
IY Last 2 digits of ISO year
I Last 1 digit of ISO year
BC or B.C. or AD or A.D. Era indicator (uppercase)
bc or b.c. or ad or a.d. Era indicator (lowercase)
MONTH Full uppercase month name
Month Full mixed-case month name
month Full lowercase month name
MON Abbreviated uppercase month name (3 characters in English, localized lengths vary)
Mon Abbreviated mixed-case month name (3 characters in English, localized lengths vary)
mon Abbreviated lowercase month name (3 characters in English, localized lengths vary)
MM Month number (01-12)
DAY Full uppercase day name
Day Full mixed-case day name
day Full lowercase day name
DY Abbreviated uppercase day name (3 characters in English, localized lengths vary)
Dy Abbreviated mixed-case day name (3 characters in English, localized lengths vary)
dy Abbreviated lowercase day name (3 characters in English, localized lengths vary)
DDD Day of year (001-366)
DD Day of month (01-31)
D Day of week (1-7. Sunday is 1)
W Week of month (1-5) (The first week starts on the first day of the month.)
WW Week number of year (1-53) (The first week starts on the first day of the year.)
IW ISO week number of year. The first Thursday of the new year is in week 1.
CC Century (2 digits). The 21st century starts on 2001-01-01.
SCC Same as CC except BC dates are prefixed by a minus sign.
J Julian Day (days since January 1, 4712 BC)
Q Quarter
RM Month in Roman numerals (I-XII. I=January) (uppercase)
rm Month in Roman numerals (i-xii. i=January) (lowercase)
RR The first 2 digits of the year when given only the last 2 digits of the year. The result is based upon an algorithm using the current year and the given 2-digit year. The first 2 digits of the given 2-digit year will be the same as the first 2 digits of the current year with the following exceptions:
  • If the given 2-digit year is < 50 and the last 2 digits of the current year is >= 50, then the first 2 digits for the given year is 1 greater than the first 2 digits of the current year.
  • If the given 2-digit year is >= 50 and the last 2 digits of the current year is < 50, then the first 2 digits for the given year is 1 less than the first 2 digits of the current year.
RRRR Only affects the TO_DATE function. Allows specification of 2-digit or 4-digit year. If 2-digit year given, then returns first 2 digits of year like RR format. If 4-digit year given, returns the given 4-digit year.
Specific modifiers may be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. The following table lists the pattern modifiers for date/time formatting.
Table 3. Template pattern modifiers for date/time formatting
Modifier Description Example
FM prefix Fill mode (suppress padding blanks and zeros) FMMonth
TH suffix Uppercase ordinal number suffix DDTH
th suffix Lowercase ordinal number suffix DDth
FX prefix Fixed format global option (see note) FX Month DD Day
SP suffix Spell mode DDSP
Note
  • FM suppresses leading zeros and trailing blanks that would otherwise be added to ensure that the output conforms to a fixed width pattern.
  • If the FX option is not used, TO_TIMESTAMP and TO_DATE skip multiple blank spaces in the input string. You must specify FX as the first item in the template. For example, TO_TIMESTAMP('2000 JUN', 'YYYY MON') is valid, but TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP only expects one space.
  • Ordinary text is allowed in TO_CHAR templates and will be output literally.
  • In conversions from string to timestamp or date, the CC field is ignored if a YYY, YYYY or Y,YYY field exists. If CC is used with the YY or Y field, the year is computed as (CC-1)*100+YY.
The following table shows the available template patterns for formatting numeric values.
Table 4. Template patterns for numeric formatting
Pattern Description
9 Value with the specified number of digits
0 Value with leading zeroes
. (period) Decimal point
, (comma) Group (thousand) separator
$ Dollar sign
PR Negative value in angle brackets
S Sign anchored to number (uses locale)
L Currency symbol (uses locale)
D Decimal point (uses locale)
G Group separator (uses locale)
MI Minus sign specified in right-most position (if number < 0)
RN or rn Roman numeral (input between 1 and 3999)
V Shift specified number of digits (see note)
Note
  • 9 results in a value with the same number of digits as there are 9s. If a digit is not available or specified, a space is output.
  • TH does not convert values less than zero or fractional numbers.

V effectively multiplies the input values by 10n (10 to the power of n), where n indicates the number of digits following V. TO_CHAR does not support the use of V combined with a decimal point. (For example, 99.9V99 is not allowed.)

The following table shows some examples about how to use TO_CHAR and TO_DATE.
Expression Result
TO CHAR(CURRENT TIMESTAMP, 'Day, DD HH12:MI:SS') 'Tuesday , 06 05:39:18'
TO CHAR(CURRENT TIMESTAMP, 'FMDay, FMDD HH12:MI:SS') 'Tuesday, 6 05:39:18'
TO CHAR(-0.1, '99.99') ' -.10'
TO CHAR(-0.1, 'FM9.99') '-.1'
TO CHAR(0.1, '0.9') ' 0.1'
TO CHAR(12, '9990999.9') ' 0012.0'
TO CHAR(12, 'FM9990999.9') '0012.'
TO CHAR(485, '999') ' 485'
TO CHAR(-485, '999') ' -485'
TO CHAR(1485, '9,999') ' 1,485'
TO CHAR(1485, '9G999') ' 1,485'
TO CHAR(148.5, '999.999') ' 148.500'
TO CHAR(148.5, 'FM999.999') '148.5'
TO CHAR(148.5, 'FM999.990') '148.500'
TO CHAR(148.5, '999D999') ' 148.500'
TO CHAR(3148.5, '9G999D999') ' 3,148.500'
TO CHAR(-485, '999S') '485- '
TO CHAR(-485, '999MI') '485- '
TO CHAR(485, '999MI') '485 '
TO CHAR(4 85, 'FM999MI') '485'
TO CHAR(-485, '999PR') '<485>'
TO CHAR(485, 'L999') '$ 485'
TO CHAR(4 85, 'RN') ' CDLXXXV'
TO CHAR(4 85, 'FMRN') 'CDLXXXV'
TO CHAR(5.2, 'FMRN') 'V'
TO CHAR(12, '99V999') ' 12000'
TO CHAR(12.4, '99V999') ' 12400'
TO CHAR(12.45, '99V9') ' 125'