PostgreSQL: to_char Function
This PostgreSQL tutorial 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
- The number, date that will be converted to a string.
- format_mask
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
YLast 3, 2, or 1 digit(s) of year IYYY 4-digit year based on the ISO standard IYY
IY
ILast 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)
Advertisements