Postgresql To_char Function
PostgreSQL: to_char Function
In this PostgreSQL post explains how to use the PostgreSQL to_char function with syntax and examples.
Description
The PostgreSQL to_char function converts a number or date to a string.
Syntax
The syntax for the to_char function in PostgreSQL is:
to_char( value, format_mask )Parameters or Arguments
- value
- format_mask
The number, date that will be converted to a string.
The format that will be used to convert value to a string. The format_mask is different whether you are converting numbers or dates. Let's take a look.
With Numbers
With numbers, the format_mask can be one of the following and can be used in many combinations.
| Parameter | Explanation |
|---|---|
| 9 | Value (with no leading zeros) |
| 0 | Value (with leading zeros) |
| . | Decimal |
| , | Group separator |
| PR | Negative value in angle brackets |
| S | Sign |
| L | Currency symbol |
| D | Decimal |
| G | Group separator |
| MI | Minus sign (for negative numbers) |
| PL | Plus sign (for positive numbers) |
| SG | Plus/minus sign (for positive and negative numbers) |
| RN | Roman numerals |
| TH | Ordinal number suffix |
| th | Ordinal number suffix |
| V | Shift digits |
| EEEE | Scientific notation |
With Dates
With dates, the format_mask can be one of the following and can be used in many combinations.
| Parameter | Explanation |
|---|---|
| YYYY | 4-digit year |
| Y,YYY | 4-digit year, with comma |
| YYY YY Y | Last 3, 2, or 1 digit(s) of year |
| IYYY | 4-digit year based on the ISO standard |
| IYY IY I | Last 3, 2, or 1 digit(s) of ISO year |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month in all uppercase |
| Mon | Abbreviated name of month capitalized |
| mon | Abbreviated name of month in all lowercase |
| MONTH | Name of month in all uppercase, padded with blanks to length of 9 characters |
| Month | Name of month capitalized, padded with blanks to length of 9 characters |
| month | Name of month in all lowercase, padded with blanks to length of 9 characters |
| RM | Month in uppercase Roman numerals |
| rm | Month in lowercase Roman numerals |
| WW | Week of year (1-53) where week 1 starts on the first day of the year |
| W | Week of month (1-5) where week 1 starts on the first day of the month |
| IW | Week of year (01-53) based on the ISO standard |
| DAY | Name of day in all uppercase, padded with blanks to length of 9 characters |
| Day | Name of day capitalized, padded with blanks to length of 9 characters |
| day | Name of day in all lowercase, padded with blanks to length of 9 characters |
| DY | Abbreviated name of day in all uppercase |
| Dy | Abbreviated name of day capitalized |
| dy | Abbreviated name of day in all lowercase |
| DDD | Day of year (1-366) |
| IDDD | Day of year based on ISO year |
| DD | Day of month (01-31) |
| D | Day of week (1-7, where 1=Sunday, 7=Saturday) |
| ID | Day of week based on ISO year (1-7, where 1=Monday, 7=Sunday) |
| J | Julian day; the number of days since midnight on November 24, 4714 BC |
| 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) |
| SSSS | Seconds past midnight (0-86399) |
| am, AM, pm, or PM | Meridian indicator |
| a.m., A.M., p.m., or P.M. | Meridian indicator |
| ad, AD, a.d., or A.D | AD indicator |
| bc, BC, b.c., or B.C. | BC indicator |
| TZ | Name of time zone in uppercase |
| tz | Name of time zone in lowercase |
| CC | 2-digit century |
Applies To
The to_char function can be used in the following versions of PostgreSQL:
- PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4
Example
Let's look at some PostgreSQL to_char function examples and explore how to use the to_char function in PostgreSQL.
Examples with Numbers
For example:
The following are number examples for the to_char function.
postgres=# SELECT to_char(1210, '9999.99');
to_char
----------
1210.00
(1 row)
postgres=# SELECT to_char(1210.7, '9G999.99');
to_char
-----------
1,210.70
(1 row)
postgres=# SELECT to_char(1210.7, 'L9G999.99');
to_char
------------
$ 1,210.70
(1 row)
postgres=# SELECT to_char(1210.7, 'L9G999');
to_char
---------
$ 1,211
(1 row)
postgres=# SELECT to_char(121, '9 9 9');
to_char
---------
1 2 1
(1 row)
postgres=# SELECT to_char(121, '00999');
to_char
---------
00121
(1 row)Examples with Dates
The following are date examples for the to_char function.
postgres=# SELECT to_char(date '2014-04-25', 'YYYY/MM/DD');
to_char
------------
2014/04/25
(1 row)
postgres=# SELECT to_char(date '2014-04-25', 'MMDDYY');
to_char
---------
042514
(1 row)
postgres=# SELECT to_char(date '2014-04-25', 'Month DD, YYYY');
to_char
--------------------
April 25, 2014
(1 row)