TO_CHAR for date formatting in PostgreSQL

Introduction

TO_CHAR helps you change how dates look by turning them into text in the style you want.

When you want to show a date in a friendly way, like 'January 1, 2024' instead of '2024-01-01'.

When you need to create reports with dates in a specific format.

When you want to combine dates with text in messages or labels.

When you want to extract parts of a date, like just the year or month, as text.

When you want to sort or group data by formatted date parts.

Syntax

PostgreSQL

TO_CHAR(date_value, 'format_pattern')

The first part is the date or timestamp you want to format.

The second part is a string that tells how to show the date, like 'YYYY' for year or 'MM' for month.

Examples

Shows the date as year-month-day, like '2024-06-15'.

PostgreSQL

TO_CHAR(current_date, 'YYYY-MM-DD')

Shows the date as full month name, day, and year, like 'June 15, 2024'.

PostgreSQL

TO_CHAR(current_date, 'Month DD, YYYY')

Shows the short day name, like 'Sat' for Saturday.

PostgreSQL

TO_CHAR(current_date, 'DY')

Shows the time in 24-hour format with minutes and seconds.

PostgreSQL

TO_CHAR(current_date, 'HH24:MI:SS')

Sample Program

This query formats the date '2024-06-15' in three different ways to show how TO_CHAR works.

PostgreSQL

SELECT TO_CHAR(DATE '2024-06-15', 'YYYY-MM-DD') AS formatted_date,
       TO_CHAR(DATE '2024-06-15', 'Month DD, YYYY') AS long_format,
       TO_CHAR(DATE '2024-06-15', 'DY') AS short_day_name;

Important Notes

Month names are padded with spaces to the right to keep the same length; use FM prefix to remove padding, e.g., 'FMMonth'.

TO_CHAR returns text, so you cannot do date math on the result without converting back.

Format patterns are case sensitive; 'YYYY' is year, 'yyyy' works the same, but 'mm' is minutes, 'MM' is month.

Summary

TO_CHAR changes dates into text with the look you want.

Use format patterns like 'YYYY', 'MM', 'DD' to pick date parts.

It helps make dates easy to read or use in reports.