Author: Written by Karel Zak on 2000-01-24.
Formatting functions provide a powerful set of tools for converting various datetypes (date/time, int, float, numeric) to formatted strings and reverse convert from formatted strings to original datetypes.
Tabla 5-6. Formatting Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| to_char(datetime, text) | text | convert datetime to string | to_char('now'::datetime, 'HH12:MI:SS') |
| to_char(timestamp, text) | text | convert timestamp to string | to_char( now(), 'HH12:MI:SS') |
| to_char(int, text) | text | convert int4/int8 to string | to_char(125, '999') |
| to_char(float, text) | text | convert float4/float8 to string | to_char(125.8, '999D9') |
| to_char(numeric, text) | text | convert numeric to string | to_char(-125.8, '999D99S') |
| to_datetime(text, text) | datetime | convert string to datetime | to_datetime('05 Dec 2000 13', 'DD Mon YYYY HH') |
| to_date(text, text) | date | convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(text, text) | date | convert string to timestamp | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | convert string to numeric | to_number('12,454.8-', '99G999D9S') |
For all formatting functions is second argument format-picture.
Tabla 5-7. Format-pictures for date/time to_char() version.
| Format-picture | Description |
|---|---|
| HH | hour of day (01-12) |
| HH12 | hour of day (01-12) |
| MI | minute (00-59) |
| SS | socond (00-59) |
| SSSS | seconds past midnight (0-86399) |
| Y,YYY | year (4 and more digits) with comma |
| YYYY | year (4 and more digits) |
| YYY | last 3 digits of year |
| YY | last 2 digits of year |
| Y | last digit of year |
| MONTH | full month name (9-letters) - all characters is upper |
| Month | full month name (9-letters) - first character is upper |
| month | full month name (9-letters) - all characters is lower |
| MON | abbreviated month name (3-letters) - all characters is upper |
| Mon | abbreviated month name (3-letters) - first character is upper |
| mon | abbreviated month name (3-letters) - all characters is lower |
| MM | month (01-12) |
| DAY | full day name (9-letters) - all characters is upper |
| Day | full day name (9-letters) - first character is upper |
| day | full day name (9-letters) - all characters is lower |
| DY | abbreviated day name (3-letters) - all characters is upper |
| Dy | abbreviated day name (3-letters) - first character is upper |
| dy | abbreviated day name (3-letters) - all characters is upper |
| DDD | day of year (001-366) |
| DD | day of month (01-31) |
| D | day of week (1-7; SUN=1) |
| W | week of month |
| WW | week number of year |
| CC | century (2-digits) |
| J | julian day (days since January 1, 4712 BC) |
| Q | quarter |
| RM | month in roman numeral (I-XII; I=JAN) |
All format-pictures allow use suffixes (postfix / prefix). The suffix is always valid for a near format-picture. The 'FX' is global prefix only.
Tabla 5-8. Suffixes for format-pictures for date/time to_char() version.
| Suffix | Description | Example |
|---|---|---|
| FM | fill mode - prefix | FMMonth |
| TH | upper ordinal number - postfix | DDTH |
| th | lower ordinal number - postfix | DDTH |
| FX | FX - (Fixed format) global format-picture switch. The TO_DATETIME / TO_DATE skip blank space if this option is not use. Must by used as first item in formt-picture. | FX Month DD Day |
| SP | spell mode (not implement now) | DDSP |
'\' - must be use as double \\, example '\\HH\\MI\\SS'
'"' - string between a quotation marks is skipen and not is parsed. If you want write '"' to output you must use \\", example '\\"YYYY Month\\"'.
text - the PostgreSQL's to_char() support text without '"', but string between a quotation marks is fastly and you have guarantee, that a text not will interpreted as a keyword (format-picture), exapmle '"Hello Year: "YYYY'.
Tabla 5-9. Format-pictures for number (int/float/numeric) to_char() version.
| Format-picture | Description |
|---|---|
| 9 | return value with the specified number of digits, and if digit is not available use blank space |
| 0 | as 9, but instead blank space use zero |
| . (period) | decimal point |
| , (comma) | group (thousand) separator |
| PR | return negative value in angle brackets |
| S | return negatice value with minus sign (use locales) |
| L | currency symbol (use locales) |
| D | decimal point (use locales) |
| G | group separator (use locales) |
| MI | return minus sign on specified position (if number < 0) |
| PL | return plus sign on specified position (if number > 0) - PostgreSQL extension |
| SG | return plus/minus sign on specified position - PostgreSQL extension |
| RN | return number as roman number (number must be between 1 and 3999) |
| TH or th | convert number to ordinal number (not convert numbers under zero and decimal numbers) - PostgreSQL extension |
| V | arg1 * (10 ^ n); - return a value multiplied by 10^n (where 'n' is number of '9's after the 'V'). The to_char() not support use 'V' and decimal poin together, example "99.9V99". |
| EEEE | science numbers. Now not supported. |
Note: A sign formatted via 'SG', 'PL' or 'MI' is not anchor in number; to_char(-12, 'S9999') produce:
' -12', but to_char(-12, 'MI9999') produce:
'- 12'. The Oracle not allow use 'MI' ahead of '9', in the Oracle must be it always after '9'.
Tabla 5-10. The to_char() examples.
| Input | Output |
|---|---|
| to_char(now(), 'Day, HH12:MI:SS') | 'Tuesday , 05:39:18' |
| to_char(now(), 'FMDay, HH12:MI:SS') | 'Tuesday, 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( 485, '9 9 9') | ' 4 8 5' |
| 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, '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( 485, 'PL999') | '+485' |
| to_char( 485, 'SG999') | '+485' |
| to_char( -485, 'SG999') | '-485' |
| to_char( -485, '9SG99') | '4-85' |
| to_char( -485, '999PR') | '<485>' |
| to_char( 485, 'L999') | 'DM 485' |
| to_char( 485, 'RN') | ' CDLXXXV' |
| to_char( 485, 'FMRN') | 'CDLXXXV' |
| to_char( 5.2, 'FMRN') | 'V' |
| to_char( 482, '999th') | ' 482nd' |
| to_char( 485, '"Good number:"999') | 'Good number: 485' |
| to_char( 485.8, '"Pre-decimal:"999" Post-decimal:" .999') | 'Pre-decimal: 485 Post-decimal: .800' |
| to_char( 12, '99V999') | ' 12000' |
| to_char( 12.4, '99V999') | ' 12400' |
| to_char( 12.45, '99V9') | ' 125' |