TO_CHAR for date formatting in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Formatting date to show full month name and year

What is the output of this query?

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

PostgreSQL

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

A 'June 2024'

B 'Jun 2024'

C '06 2024'

D '2024-06-15'

💡 Hint

The format 'Month' returns the full month name padded with spaces to 9 characters.

The 'Month' format returns the full month name padded with spaces to 9 characters, so 'June' is padded with spaces to 9 characters, followed by a space and the year.

❓ query_result

intermediate

Using TO_CHAR to format time with AM/PM

What is the output of this query?

SELECT TO_CHAR(TIMESTAMP '2024-06-15 14:30:00', 'HH12:MI AM');

PostgreSQL

SELECT TO_CHAR(TIMESTAMP '2024-06-15 14:30:00', 'HH12:MI AM');

A '02:30 AM'

B '02:30 PM'

C '14:30 PM'

D '14:30'

💡 Hint

HH12 returns hour in 12-hour format, AM shows AM or PM.

14:30 in 24-hour time is 2:30 PM in 12-hour format, so output is '02:30 PM'.

📝 Syntax

advanced

Identify the syntax error in TO_CHAR date format

Which option contains a syntax error in the TO_CHAR format string?

Query example:
SELECT TO_CHAR(DATE '2024-06-15', '');

A 'YYYY-MM-DD'

B 'YYYY/MM/DD'

C 'YYYY-MM-DD-DX'

D 'YY-MM-DD'

💡 Hint

Check if the format string uses valid date patterns.

The format 'YYYY-MM-DD-DX' contains invalid 'DX' which causes a syntax error.

❓ query_result

advanced

Output of TO_CHAR with day of week and day number

What is the output of this query?

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

PostgreSQL

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

A 'Sat 15th'

B 'Saturday 15'

C 'Su 15'

D 'Sat 15'

💡 Hint

'Dy' returns abbreviated day name, 'DD' returns day of month as two digits.

'2024-06-15' is a Saturday, so 'Dy' returns 'Sat' and 'DD' returns '15'.

🧠 Conceptual

expert

Understanding padding behavior in TO_CHAR date formatting

Which statement about TO_CHAR date formatting padding is TRUE?

A The 'Month' format pads the month name with spaces to 9 characters by default.

B The 'Mon' format pads the month abbreviation with zeros to 3 characters.

C The 'DD' format pads the day number with spaces to 2 characters.

D The 'YYYY' format pads the year with spaces to 5 characters.

💡 Hint

Check how TO_CHAR handles padding for month names and abbreviations.

The 'Month' format pads the full month name with spaces to 9 characters. 'Mon' does not pad with zeros, 'DD' pads with zeros, and 'YYYY' returns 4 digits without padding.

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