| | The EnterpriseDB formatting functions
provide a powerful set of tools for converting various data types
(date/time, integer, floating point, numeric) to formatted strings
and for converting from formatted strings to specific data types.
Table 8-19 lists them.
These functions all follow a common calling convention: the first
argument is the value to be formatted and the second argument is a
template that defines the output or input format.
Table 8-19. Formatting Functions | Function | Return Type | Description | Example |
|---|
to_char(DATE, VARCHAR) | VARCHAR | convert a value of DATE datatype to VARCHAR datatype | to_char(sysdate, 'HH12:MI:SS') | to_char(INT, VARCHAR) | VARCHAR | convert INTEGER to VARCHAR | to_char(125, '999') | to_char(DOUBLE PRECISION,
VARCHAR) | VARCHAR | convert REAL/DOUBLE PRECISION to VARCHAR | to_char(125.8::real, '999D9') | to_char(NUMERIC, VARCHAR) | VARCHAR | convert >NUMERIC to VARCHAR | to_char(-125.8, '999D99S') | to_date(VARCHAR, VARCHAR) | TIMESTAMP | TIMESTAMP is used to allow compatibility with Redwood Dates. Redwood dates return TIME along
with DATE | to_date('05 Dec 2000', 'DD Mon YYYY') | to_number(text, text) | numeric | convert string to numeric | to_number('12,454.8-', '99G999D9S') | to_timestamp(VARCHAR, VARCHAR) | TIMESTAMP | convert VARCHAR to TIME STAMP | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
In an output template string (for to_char), there are certain patterns that are
recognized and replaced with appropriately-formatted data from the value
to be formatted. Any text that is not a template pattern is simply
copied verbatim. Similarly, in an input template string (for anything but to_char), template patterns
identify the parts of the input data string to be looked at and the
values to be found there.
Table 8-20 shows the
template patterns available for formatting date values using the
to_char and to_date functions.
Table 8-20. Template Date/Time Format Patterns for the to_char and to_date Functions | 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) | | MS | Millisecond (000-999) | | US | Microsecond (000000-999999) | | 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 chars in English, localized lengths vary) | | Mon | Abbreviated mixed-case month name (3 chars in English, localized lengths vary) | | mon | Abbreviated lowercase month name (3 chars 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 chars in English, localized lengths vary) | | Dy | Abbreviated mixed-case day name (3 chars in English, localized lengths vary) | | dy | Abbreviated lowercase day name (3 chars 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 | First 2 digits of the year when given only the last 2 digits of the year.
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 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. | | TZ | Time-zone name (uppercase) | | tz | Time-zone name (lowercase) |
Table 8-21shows the format masks that are available for the round function.
Table 8-21. Template Round Patterns | Pattern | Description |
|---|
| CC , SSC | Returns January 1, cc01 where cc is first 2 digits of the given year if last 2 digits <= 50,
or 1 greater than the first 2 digits of the given year if last 2 digits > 50; (for AD years) | | SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y | Returns January 1, yyyy where yyyy is rounded to the nearest year; rounds down on June 30, rounds up on July 1 | | IYYY, IYY, IY, I | Rounds to the beginning of the ISO year which is determined by rounding down if the month and day is
on or before June 30th, or by rounding up if the month and day is July 1st or later | | Q | Returns the first day of the quarter determined by rounding down if the month and day is on or before the
15th of the second month of the quarter, or by rounding up if the month and day is on the 16th of the second month
or later of the quarter | | MONTH, MON, MM, RM | Returns the first day of the specified month if the day of the month is on or prior to the 15th;
returns the first day of the following month if the day of the month is on the 16th or later | | WW | Round to the nearest date that corresponds to the same day of the week as the first day of the year | | IW | Round to the nearest date that corresponds to the same day of the week as the first day of the ISO year | | W | Round to the nearest date that corresponds to the same day of the week as the first day of the month | | DDD, DD, J | Rounds to the start of the nearest day; 11:59:59 AM or earlier rounds to the start of the same day; 12:00:00 PM
or later rounds to the start of the next day | | DAY, DY, D | Rounds to the nearest Sunday | | HH, HH12, HH24 | Round to the nearest hour | | MI | Round to the nearest minute |
Certain modifiers may be applied to any template pattern to alter its
behavior. For example, FMMonth
is the Month pattern with the
FM modifier.
Table 8-22 shows the
modifier patterns for date/time formatting.
Table 8-22. Template Pattern Modifiers for Date/Time Formatting | Modifier | Description | Example |
|---|
| FM prefix | fill mode (suppress padding blanks and zeroes) | FMMonth | | TH suffix | uppercase ordinal number suffix | DDTH | | th suffix | lowercase ordinal number suffix | DDth | | FX prefix | fixed format global option (see usage notes) | FX Month DD Day | | TM prefix | translation mode (print localized day and month names based on lc_messages) | TMMonth | | SP suffix | spell mode (not yet implemented) | DDSP |
Usage notes for the date/time formatting:
FM suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
fixed-width.
TM does not include trailing blanks.
to_timestamp and to_date
skip multiple blank spaces in the input string if the FX option
is not used. FX must be specified as the first item
in the template. For example
to_timestamp('2000 JUN', 'YYYY MON') is correct, but
to_timestamp('2000 JUN', 'FXYYYY MON') returns an error,
because to_timestamp expects one space only.
Ordinary text is allowed in to_char
templates and will be output literally. You can put a substring
in double quotes to force it to be interpreted as literal text
even if it contains pattern key words. For example, in
'"Hello Year "YYYY', the YYYY
will be replaced by the year data, but the single Y in Year
will not be.
If you want to have a double quote in the output you must
precede it with a backslash, for example '\\"YYYY
Month\\"'.
(Two backslashes are necessary because the backslash already
has a special meaning in a string constant.)
The YYYY conversion from string to TIMESTAMP or
DATE has a restriction if you use a year with more than 4 digits. You must
use some non-digit character or template after YYYY,
otherwise the year is always interpreted as 4 digits. For example
(with the year 20000):
to_date('200001131', 'YYYYMMDD') will be
interpreted as a 4-digit year; instead use a non-digit
separator after the year, like
to_date('20000-1131', 'YYYY-MMDD') or
to_date('20000Nov31', 'YYYYMonDD').
Millisecond (MS) and microsecond (US)
values in a conversion from string to TIMESTAMP are used as part of the
seconds after the decimal point. For example
to_timestamp('12:3', 'SS:MS') is not 3 milliseconds,
but 300, because the conversion counts it as 12 + 0.3 seconds.
This means for the format SS:MS, the input values
12:3, 12:30, and 12:300 specify the
same number of milliseconds. To get three milliseconds, one must use
12:003, which the conversion counts as
12 + 0.003 = 12.003 seconds.
Here is a more
complex example:
to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')
is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
1230 microseconds = 2.021230 seconds.
Table 8-23 shows the
template patterns available for formatting numeric values.
Table 8-23. 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 in specified position (if number < 0) | | PL | Plus sign in specified position (if number > 0) | | SG | Plus/minus sign in specified position | | RN or rn | Roman numeral (input between 1 and 3999) | | TH or th | Ordinal number suffix | | V | Shift specified number of digits (see notes) | | EEEE | Scientific notation (not implemented yet) |
Usage notes for the numeric formatting:
A sign formatted using SG, PL, or
MI is not anchored to
the number; for example,
to_char(-12, 'S9999') produces ' -12',
but to_char(-12, 'MI9999') produces '- 12'.
9 results in a value with the same number of
digits as there are 9s. If a digit is
not available it outputs a space.
TH does not convert values less than zero
and does not convert fractional numbers.
PL, SG, and
TH are EnterpriseDB
extensions.
V effectively
multiplies the input values by
10^n, where
n is the number of digits following
V.
to_char does not support the use of
V combined with a decimal point.
(E.g., 99.9V99 is not allowed.)
Table 8-24 shows some
examples of the use of the to_char and
to_date functions.
Table 8-24. to_char and to_date function Examples | Expression | Result |
|---|
| to_char(current_timestamp, 'Day, DD HH12:MI:SS') | 'Tuesday , 06 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(148.5, '999.999') | ' 148.500' | | to_char(148.5, 'FM999.999') | '148.5' | | to_char(148.5, 'FM999.990') | '148.500' | | to_char(-485, '999S') | '485-' | | to_char(-485, '999MI') | '485-' | | to_char(485, '999MI') | '485 ' | | to_char(485, 'FM999MI') | '485' | | to_char(485, 'L999') | '$ 485 | | to_char(485, 'RN') | ' CDLXXXV' | | to_char(485, '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' | | to_number(0, '99.9') | '.00' | | to_number(-0.2, '99.9') | '-.20' |
| |
|---|
