TO_CHAR for date formatting in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to format the current date as 'YYYY-MM-DD'.

PostgreSQL

SELECT TO_CHAR(CURRENT_DATE, [1]);

Drag options to blanks, or click blank then click option'

A'DD/MM/YYYY'

B'YYYY-MM-DD'

C'MM-DD-YYYY'

D'YY-MM-DD'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong order of year, month, and day.

Forgetting to put the format string in quotes.

The format string 'YYYY-MM-DD' outputs the date in year-month-day format, which is the standard ISO format.

2fill in blank

medium

Complete the code to format the current timestamp as 'HH24:MI:SS'.

PostgreSQL

SELECT TO_CHAR(CURRENT_TIMESTAMP, [1]);

Drag options to blanks, or click blank then click option'

A'HH12:MI:SS AM'

B'HH:MM:SS'

C'HH24:MI:SS'

D'HH24:MM:SS'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MM' instead of 'MI' for minutes.

Using 12-hour format when 24-hour is needed.

'HH24:MI:SS' formats the time in 24-hour format with minutes and seconds. 'MI' is minutes, not 'MM'.

3fill in blank

hard

Fix the error in the code to format the date as 'Month DD, YYYY'.

PostgreSQL

SELECT TO_CHAR(CURRENT_DATE, [1]);

Drag options to blanks, or click blank then click option'

A'Month DD, YYYY'

B'month DD, YYYY'

C'MONTH DD, YYYY'

D'Mon DD, YYYY'

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase 'month' which returns all lowercase month name.

Using 'Mon' which returns abbreviated month name.

'Month' capitalizes the first letter and spells out the full month name. Lowercase 'month' returns all lowercase, uppercase 'MONTH' returns all uppercase.

4fill in blank

hard

Fill both blanks to format the timestamp as 'YYYY-MM-DD HH24:MI:SS'.

PostgreSQL

SELECT TO_CHAR(NOW(), [1] || ' ' || [2]);

Drag options to blanks, or click blank then click option'

A'YYYY-MM-DD'

B'HH24:MI:SS'

C'MM-DD-YYYY'

D'HH12:MI:SS AM'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 12-hour format instead of 24-hour.

Mixing up the order of date and time formats.

We combine the date format 'YYYY-MM-DD' and the time format 'HH24:MI:SS' separated by a space to get the full timestamp format.

5fill in blank

hard

Fill all three blanks to format the date as 'Day, DD Month'.

PostgreSQL

SELECT TO_CHAR(CURRENT_DATE, [1] || ', ' || [2] || ' ' || [3]);

Drag options to blanks, or click blank then click option'

A'Day'

B'DD'

C'Month'

D'YYYY'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Mon' instead of 'Month' for full month name.

Forgetting the comma after the day name.

'Day' gives the full day name padded to 9 characters, 'DD' is day number, and 'Month' is full month name with first letter capitalized.

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