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.

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