to_char() function in SQL

The to_char() function in SQL is a versatile tool used to convert a number or a timestamp into a string based on a specified format. This function is exceptionally useful for presenting data in a more readable or expected format, facilitating easier interpretation and reporting.

Syntax

The basic syntax of the to_char() function is as follows:

to_char(value, format)

• value: This is the number or date that you want to convert.
• format: A text string that defines the output format. The format varies depending on whether you’re formatting a timestamp or a number.

Formatting Dates

When used with dates, the to_char() function allows you to specify the exact way you want the date to be represented.

Example: Formatting user’s signup date

Suppose you want to display the signup_date from the users table in a more friendly format (e.g., “January 15, 2023”):

SELECT
  id,
  to_char(signup_date, 'Month DD, YYYY') AS formatted_signup_date
FROM users
LIMIT 5

Here, the format string Month DD, YYYY specifies that you want the full name of the month followed by the day and the year.

Formatting Numbers

The to_char() function can also format numeric values, turning them into strings with specified patterns, such as adding leading zeroes, commas, or fixed decimal places.

Example: Formatting product prices

Suppose you want to format the price in the products table to include two decimal places and commas for thousands:

SELECT
  id,
  name,
  to_char(price, 'FM$9,999,999.00') AS formatted_price
FROM products

In this format string, FM is used to suppress leading spaces, $ adds a dollar sign, and commas are used for thousands while .00 ensures two decimal places.

Practical Use Cases

• Report Generation: Use to_char() to generate readable dates or numeric values for reports.
• User Interfaces: Present values in a web or app interface in a user-friendly manner.
• Data Exporting: Ensure that dates and numbers are presented in a consistent format when exporting data to CSV, Excel, etc.

More on Date and Number Formatting

Here’s a list of all formatting tokens available to you for formatting dates and timestamps:

Date Formatting Tokens

Let’s try to use as many of them to illustrate how to format time values:

SELECT
  id,
  created_at,
  to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS iso_format,
  to_char(created_at, 'Month DD, YYYY at HH12:MI:SS AM') AS readable_format,
  to_char(created_at, 'Dy, DD Mon YYYY (HH24:MI)') AS compact_format,
  to_char(created_at, 'YYYY"Q"Q W"W" HH24:MI:SS TZ') AS analytical_format
FROM users
LIMIT 5

This query demonstrates multiple formatting styles:

• Standard ISO format with 24-hour time
• Human-readable format with month name, 12-hour clock and AM/PM indicator
• Compact format with abbreviated day and month
• Analytical format showing year, quarter, week of year, and timezone

Number Formatting Tokens

Let’s see how to use these number formatting tokens:

SELECT
  id,
  price,
  to_char(price, '999,999.99') AS formatted_price,
  to_char(price, '$999,999.00') AS dollar_price,
  to_char(price, 'L999G999D99') AS locale_price,
  to_char(price, '999999MI') AS price_with_trailing_sign,
  to_char(price, 'S999999.99') AS price_with_leading_sign,
  to_char(price * -1, '999999PR') AS negative_price_parentheses,
  to_char(price, 'FM999999.99') AS price_without_padding,
  to_char(CAST(price AS integer), '999TH') AS ordinal_price
FROM products
LIMIT 5

For more complex formatting, we can combine multiple tokens:

SELECT
  id,
  amount_usd,
  to_char(amount_usd, 'FM$999,999,999.00') AS clean_currency,
  to_char(CAST(amount_usd AS integer), 'RN') AS roman_numeral,
  to_char(amount_usd, '"Total: $"FM999,999,999.00" (USD)"') AS report_format,
  CASE
    WHEN amount_usd < 0 THEN to_char(ABS(amount_usd), '$999,999.99PR')
    ELSE to_char(amount_usd, '$999,999.99')
  END AS accounting_format
FROM transactions
LIMIT 5

These examples demonstrate how to:

• Format prices with thousands separators and decimal places
• Add currency symbols (both fixed and locale-specific)
• Display negative numbers with trailing minus signs or parentheses
• Remove padding with the FM modifier
• Convert numbers to ordinal format or Roman numerals
• Create complex custom formats with text and numbers combined
• Format financial data in accounting style

Summary

As you can see, the to_char() function is a powerhouse! It can format both dates and numbers, ensuring that your SQL queries can produce output strings that are precise and human-friendly.