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

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

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

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

Hint

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

Practice

1. What does the PostgreSQL function TO_CHAR do when used with a date?

easy

A. It converts the date into a formatted text string.

B. It changes the date into a number representing days.

C. It adds days to the date value.

D. It deletes the date from the database.

Solution

1. Step 1: Understand TO_CHAR purpose

   The TO_CHAR function is used to convert dates or numbers into text with a specific format.

2. Step 2: Apply to date values

   When used with a date, it changes the date into a string that looks like the format you choose (e.g., 'YYYY-MM-DD').

3. Final Answer:

   It converts the date into a formatted text string. -> Option A

4. Quick Check:

   TO_CHAR formats date to text [OK]

Hint: TO_CHAR turns dates into readable text formats [OK]

Common Mistakes:

• Thinking TO_CHAR changes the date value itself
• Confusing TO_CHAR with date arithmetic functions
• Assuming TO_CHAR returns a number

2. Which of the following is the correct syntax to format the current date as 'YYYY-MM-DD' using TO_CHAR in PostgreSQL?

easy

A. SELECT TO_CHAR(CURRENT_DATE, "YYYY-MM-DD");

B. SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');

C. SELECT TO_CHAR(CURRENT_DATE, YYYY-MM-DD);

D. SELECT TO_CHAR('YYYY-MM-DD', CURRENT_DATE);

Solution

1. Step 1: Check TO_CHAR function syntax

   The correct syntax is TO_CHAR(date_value, 'format_string') where the format is a string in single quotes.

2. Step 2: Identify correct option

   SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'); uses the date first, then the format string in single quotes, which is correct.

3. Final Answer:

   SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'); -> Option B

4. Quick Check:

   TO_CHAR(date, 'format') syntax [OK]

Hint: Format string must be in single quotes after date [OK]

Common Mistakes:

• Swapping arguments order
• Missing quotes around format string
• Using double quotes instead of single quotes

3. What is the output of this query?

SELECT TO_CHAR(DATE '2024-06-15', 'FMMonth DD, YYYY');

medium

A. June 15,2024

B. June 15 2024

C. June 15, 2024

D. June15, 2024

Solution

1. Step 1: Understand format pattern

   The format 'FMMonth DD, YYYY' means full month name without padding, day with two digits, comma, space, then four-digit year.

2. Step 2: Apply to given date

   For '2024-06-15', the month is June, day 15, year 2024, so output is 'June 15, 2024'.

3. Final Answer:

   June 15, 2024 -> Option C

4. Quick Check:

   FMMonth DD, YYYY = June 15, 2024 [OK]

Hint: FMMonth avoids space padding, watch commas carefully [OK]

Common Mistakes:

• Forgetting FM modifier causing space padding in 'Month'
• Missing comma or space after day
• Confusing 'Month' with 'Mon' abbreviation

4. Identify the error in this query:

SELECT TO_CHAR(NOW(), YYYY-MM-DD);

medium

A. NOW() cannot be used with TO_CHAR.

B. TO_CHAR requires three arguments.

C. YYYY-MM-DD is not a valid format pattern.

D. Missing single quotes around the format string.

Solution

1. Step 1: Check format string usage

   The format pattern must be a string enclosed in single quotes.

2. Step 2: Identify error in query

   The query uses YYYY-MM-DD without quotes, causing a syntax error.

3. Final Answer:

   Missing single quotes around the format string. -> Option D

4. Quick Check:

   Format string needs quotes [OK]

Hint: Always put format patterns in single quotes [OK]

Common Mistakes:

• Forgetting quotes around format
• Thinking NOW() is incompatible
• Adding extra arguments to TO_CHAR

5. You want to display the current timestamp as 'DD/MM/YYYY HH24:MI' (day/month/year hour:minute). Which query correctly formats it using TO_CHAR?

hard

A. SELECT TO_CHAR(NOW(), 'DD/MM/YYYY HH24:MI');

B. SELECT TO_CHAR(NOW(), 'MM-DD-YYYY HH:MI');

C. SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD-MM-YYYY HH24:MI');

D. SELECT TO_CHAR(CURRENT_DATE, 'DD/MM/YYYY HH24:MI');

Solution

1. Step 1: Identify correct format pattern

   The required format is day/month/year with slashes and 24-hour time with minutes: 'DD/MM/YYYY HH24:MI'.

2. Step 2: Choose correct function and format

   Use NOW() or CURRENT_TIMESTAMP for current timestamp. SELECT TO_CHAR(NOW(), 'DD/MM/YYYY HH24:MI'); matches the format exactly.

3. Final Answer:

   SELECT TO_CHAR(NOW(), 'DD/MM/YYYY HH24:MI'); -> Option A

4. Quick Check:

   Exact format with NOW() and slashes [OK]

Hint: Match format string exactly with desired output [OK]

Common Mistakes:

• Using CURRENT_DATE which lacks time
• Mixing date separators (slashes vs dashes)
• Using 12-hour format without AM/PM