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.