TO_CHAR for date formatting in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TO_CHAR function do when used with dates in PostgreSQL?

TO_CHAR converts a date or timestamp into a string formatted according to the pattern you specify.

beginner

How would you format a date to show only the year using TO_CHAR?

Use TO_CHAR(date_column, 'YYYY') to get the year as a four-digit string.

beginner

What format pattern would you use with TO_CHAR to display the full month name?

Use 'FMMonth' in the format string to display the full month name without padding, e.g., TO_CHAR(date_column, 'FMMonth').

intermediate

Explain the difference between 'MM' and 'Mon' in TO_CHAR date formatting.

'MM' returns the month as a two-digit number (01-12), while 'Mon' returns the abbreviated month name (Jan, Feb, etc.).

beginner

How can TO_CHAR help when you want to display a date in a custom format like 'DD-MM-YYYY'?

You specify the format string 'DD-MM-YYYY' in TO_CHAR to get the date as day-month-year with dashes, e.g., TO_CHAR(date_column, 'DD-MM-YYYY').

Which TO_CHAR format pattern returns the full name of the day of the week?

A 'Day'

B 'Dy'

C 'DD'

D 'D'

The pattern 'Day' returns the full day name (e.g., Monday). 'Dy' returns abbreviated day name.

What will TO_CHAR(current_date, 'YYYY-MM-DD') return?

A Month-Day-Year as numbers separated by dashes

B A timestamp with time included

C Day-Month-Year as numbers separated by dashes

D Year-Month-Day as numbers separated by dashes

The format 'YYYY-MM-DD' returns year, month, and day in that order separated by dashes.

If you want to show the month as a two-digit number, which format should you use?

A 'Mon'

B 'MM'

C 'Month'

D 'YY'

'MM' returns the month as a two-digit number (01 to 12).

Which TO_CHAR pattern returns the last two digits of the year?

A 'YYYY'

B 'Y'

C 'YY'

D 'Year'

'YY' returns the last two digits of the year, e.g., '23' for 2023.

What does TO_CHAR(timestamp, 'HH24:MI:SS') format?

A Hour in 24-hour format with minutes and seconds

B Hour in 12-hour format with minutes and seconds

C Only minutes and seconds

D Only hour and seconds

'HH24:MI:SS' formats time in 24-hour format with minutes and seconds.

Describe how TO_CHAR can be used to format a date to show the day, abbreviated month, and year.

Think about how to combine day, month abbreviation, and year in one format.

Explain the difference between 'MM' and 'Mon' in TO_CHAR date formatting and when you might use each.

Consider readability vs sorting needs.

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