TO_CHAR for date formatting in PostgreSQL - Mini Project: Build & Apply

Format Dates Using TO_CHAR in PostgreSQL

📖 Scenario: You work in a company database where dates are stored in a standard format. You want to display these dates in a friendlier way for reports.

🎯 Goal: Learn how to use the TO_CHAR function in PostgreSQL to format dates into readable strings.

📋 What You'll Learn

Create a table called events with an id column and a event_date column of type DATE.

Insert three rows with specific dates into the events table.

Create a variable or setting for the desired date format string.

Write a query that selects the id and formats event_date using TO_CHAR with the format string.

Add an ORDER BY clause to sort the results by the formatted date.

💡 Why This Matters

🌍 Real World

Formatting dates nicely is important for reports, user interfaces, and exporting data where human-friendly date formats improve readability.

💼 Career

Database developers and analysts often need to format dates for reports and applications using functions like TO_CHAR in PostgreSQL.

1

Create the events table with dates

Create a table called events with columns id as integer and event_date as date. Then insert these three rows exactly: (1, '2024-01-15'), (2, '2024-06-30'), (3, '2024-12-25').

PostgreSQL

-- Create table and insert rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the date format string

Create a variable or setting called date_format and set it to the string 'FMMonth DD, YYYY' which will format dates like 'January 15, 2024'.

PostgreSQL

CREATE TABLE events (id INTEGER, event_date DATE);
INSERT INTO events (id, event_date) VALUES
(1, '2024-01-15'),
(2, '2024-06-30'),
(3, '2024-12-25');

-- Define the date format string
-- Your code here

Need a hint?

Use \set in psql to create a variable for the format string.

3

Write a query using TO_CHAR with the format string

Write a SELECT query that retrieves id and formats event_date using TO_CHAR(event_date, :'date_format') and alias it as formatted_date.

PostgreSQL

CREATE TABLE events (id INTEGER, event_date DATE);
INSERT INTO events (id, event_date) VALUES
(1, '2024-01-15'),
(2, '2024-06-30'),
(3, '2024-12-25');

\set date_format 'FMMonth DD, YYYY'

-- Write SELECT query using TO_CHAR with the date_format variable
-- Your code here

Need a hint?

Use TO_CHAR(event_date, :'date_format') to format the date using the variable.

4

Sort the results by the formatted date

Add an ORDER BY clause to the previous SELECT query to sort the results by formatted_date in ascending order.

PostgreSQL

CREATE TABLE events (id INTEGER, event_date DATE);
INSERT INTO events (id, event_date) VALUES
(1, '2024-01-15'),
(2, '2024-06-30'),
(3, '2024-12-25');

\set date_format 'FMMonth DD, YYYY'

SELECT id, TO_CHAR(event_date, :'date_format') AS formatted_date FROM events
-- Add ORDER BY clause to sort by formatted_date
-- Your code here

Need a hint?

Use ORDER BY formatted_date ASC to sort the results by the formatted date.