DATE_FORMAT function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of DATE_FORMAT with mixed specifiers

What is the output of the following query?

SELECT DATE_FORMAT('2024-06-15 14:35:20', '%W, %M %d, %Y %r') AS formatted_date;

MySQL

SELECT DATE_FORMAT('2024-06-15 14:35:20', '%W, %M %d, %Y %r') AS formatted_date;

A Saturday, 06 15, 2024 02:35:20 PM

B Sunday, June 15, 2024 14:35:20

C Saturday, June 15, 2024 02:35:20 PM

D Saturday, June 15, 2024 14:35:20

💡 Hint

Remember %W gives weekday name and %r gives 12-hour time with AM/PM.

The date '2024-06-15' is a Saturday. %W returns full weekday name, %M full month name, %d day with leading zero, %Y full year, and %r 12-hour time with seconds and AM/PM.

🧠 Conceptual

intermediate

Understanding DATE_FORMAT specifiers

Which DATE_FORMAT specifier returns the abbreviated month name (e.g., 'Jun')?

A %b

B %M

C %W

D %m

💡 Hint

Look for the specifier that returns a short month name.

%b returns abbreviated month name like 'Jan', 'Feb', 'Jun'. %M returns full month name, %m returns month number with leading zero, %W returns full weekday name.

📝 Syntax

advanced

Identify the syntax error in DATE_FORMAT usage

Which option contains a syntax error in using DATE_FORMAT function?

MySQL

SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d') AS date1;

A SELECT DATE_FORMAT('2024-06-15', '%Y/%m/%d') AS date2;

B SELECT DATE_FORMAT('2024-06-15', '%d-%m-%Y') AS date3;

C SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d') AS date1;

D SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d);

💡 Hint

Check for missing quotes or parentheses.

Option D is missing the closing quote for the format string, causing a syntax error. All others are syntactically correct.

❓ optimization

advanced

Optimizing date formatting for performance

You want to display only the year and month from a large table's datetime column using DATE_FORMAT. Which query is the most efficient?

A SELECT DATE_FORMAT(datetime_col, '%Y-%m') FROM big_table;

B SELECT CONCAT(YEAR(datetime_col), '-', MONTH(datetime_col)) FROM big_table;

C SELECT LEFT(DATE_FORMAT(datetime_col, '%Y-%m-%d'), 7) FROM big_table;

D SELECT SUBSTRING(DATE_FORMAT(datetime_col, '%Y-%m-%d'), 1, 7) FROM big_table;

💡 Hint

Consider readability and function overhead.

Using DATE_FORMAT with '%Y-%m' directly extracts year and month in one step, which is clear and efficient. Options A, B, and D add extra function calls or string manipulation, which is less efficient.

🔧 Debug

expert

Debugging unexpected output from DATE_FORMAT

A developer runs:

SELECT DATE_FORMAT('2024-06-15 23:59:59', '%h:%i %p') AS time_12h;

But the output is '11:59 PM' as expected. They want to show '23:59 PM'. Which change will cause the output to show '23:59 PM'?

A Change '%i' to '%I' in the format string.

B Change '%h' to '%H' in the format string.

C Remove '%p' from the format string.

D Change '%p' to '%P' in the format string.

💡 Hint

%h is 12-hour format, %H is 24-hour format.

The specifier '%h' returns hour in 12-hour format (01..12). '%H' returns hour in 24-hour format (00..23). To get '23:59 PM' (though PM is redundant with 24-hour), changing '%h' to '%H' will show '23:59'. The '%p' will still show AM/PM but is not typical with 24-hour format.