DATE_FORMAT function in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to format the date as 'Year-Month-Day'.

MySQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date FROM orders;

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

A'%Y-%m-%d'

B'%d/%m/%Y'

C'%m-%d-%Y'

D'%H:%i:%s'

Attempts:

3 left

💡 Hint

Common Mistakes

Using slashes instead of dashes in the format string.

Using time format specifiers instead of date.

The format string '%Y-%m-%d' outputs the date as Year-Month-Day, which is the desired format.

2fill in blank

medium

Complete the code to display the date as 'Month name Day, Year' (e.g., June 15, 2024).

MySQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date FROM orders;

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

A'%d-%m-%Y'

B'%M %e, %Y'

C'%m %d %Y'

D'%Y/%m/%d'

Attempts:

3 left

💡 Hint

Common Mistakes

Using %m instead of %M for month name.

Using %d which adds leading zero to day.

The format string '%M %e, %Y' outputs the full month name, day of the month without leading zero, and full year, matching the desired format.

3fill in blank

hard

Fix the error in the code to correctly format the date as 'Day/Month/Year'.

MySQL

SELECT DATE_FORMAT(order_date, '[1]') AS formatted_date FROM orders;

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

A'%d/%m/%Y'

B'%d-%m-%Y'

C'%Y/%m/%d'

D'%m/%d/%Y'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping month and day positions.

Using dashes instead of slashes.

The correct format for 'Day/Month/Year' is '%d/%m/%Y'. This places day first, then month, then year separated by slashes.

4fill in blank

hard

Fill both blanks to format the date as 'Hour:Minute AM/PM' (e.g., 03:45 PM).

MySQL

SELECT DATE_FORMAT(order_time, '[1]:[2] %p') AS formatted_time FROM orders;

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

A%h

B%i

C%H

D%m

Attempts:

3 left

💡 Hint

Common Mistakes

Using %H instead of %h for 12-hour format.

Using %m which is month, not minutes.

%h gives the hour in 12-hour format with leading zero, and %i gives minutes with leading zero. %p adds AM or PM.

5fill in blank

hard

Fill all three blanks to format the date as 'Weekday, Month Day, Year' (e.g., Monday, June 15, 2024).

MySQL

SELECT DATE_FORMAT(order_date, '[1], [2] [3], %Y') AS formatted_date FROM orders;

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

A%W

B%M

C%e

D%d

Attempts:

3 left

💡 Hint

Common Mistakes

Using %d instead of %e for day.

Using abbreviated weekday instead of full name.

%W gives the full weekday name, %M the full month name, and %e the day of the month without leading zero, matching the desired format.

Practice

1. What does the MySQL function DATE_FORMAT(date, format) do?

easy

A. It calculates the difference between two dates.

B. It converts a string to a date type.

C. It adds days to a date value.

D. It changes how a date or time is displayed using a specific pattern.

Solution

1. Step 1: Understand the function purpose

   The DATE_FORMAT function is used to display dates in a specific format by applying a pattern string.

2. Step 2: Compare options with function behavior

   Only It changes how a date or time is displayed using a specific pattern. correctly describes changing the display format of a date/time value.

3. Final Answer:

   It changes how a date or time is displayed using a specific pattern. -> Option D

4. Quick Check:

   DATE_FORMAT changes display format = A [OK]

Hint: DATE_FORMAT changes date display style, not date value [OK]

Common Mistakes:

• Confusing DATE_FORMAT with date arithmetic functions
• Thinking it converts strings to dates
• Assuming it calculates date differences

2. Which of the following is the correct syntax to format a date as 'Year-Month-Day' using DATE_FORMAT?

easy

A. DATE_FORMAT(date, '%Y-%m-%d')

B. DATE_FORMAT(date, 'YYYY-MM-DD')

C. DATE_FORMAT(date, '%y/%M/%d')

D. DATE_FORMAT(date, 'YY-MM-DD')

Solution

1. Step 1: Recall correct format specifiers

   In MySQL, '%Y' is 4-digit year, '%m' is 2-digit month, '%d' is 2-digit day.

2. Step 2: Check each option's format string

   Only DATE_FORMAT(date, '%Y-%m-%d') uses correct MySQL format specifiers and separators for 'Year-Month-Day'. Others use invalid or wrong specifiers.

3. Final Answer:

   DATE_FORMAT(date, '%Y-%m-%d') -> Option A

4. Quick Check:

   Use %Y-%m-%d for year-month-day [OK]

Hint: Use %Y for year, %m for month, %d for day in DATE_FORMAT [OK]

Common Mistakes:

• Using uppercase YYYY instead of %Y
• Mixing format codes from other languages
• Using wrong separators like slashes instead of dashes

3. Given the query:
SELECT DATE_FORMAT('2024-06-15 14:30:00', '%W, %M %d, %Y %r') AS formatted_date;
What is the expected output in the formatted_date column?

medium

A. Sun, Jun 15, 2024 02:30 PM

B. Saturday, June 15, 2024 02:30:00 PM

C. Saturday, June 15, 2024 14:30:00

D. 2024-06-15 14:30:00

Solution

1. Step 1: Understand each format specifier

   %W = full weekday name (Saturday), %M = full month name (June), %d = day (15), %Y = year (2024), %r = 12-hour time with seconds and AM/PM (02:30:00 PM).

2. Step 2: Apply format to given datetime

   The datetime '2024-06-15 14:30:00' is 2:30 PM in 12-hour format, so output matches Saturday, June 15, 2024 02:30:00 PM exactly.

3. Final Answer:

   Saturday, June 15, 2024 02:30:00 PM -> Option B

4. Quick Check:

   Full weekday, month, and 12-hour time = C [OK]

Hint: Remember %r gives 12-hour time with seconds and AM/PM [OK]

Common Mistakes:

• Confusing %r with 24-hour time %H:%i:%s
• Using abbreviated weekday (%a) instead of full (%W)
• Ignoring AM/PM in output

4. Identify the error in this query:
SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d %H:%i:%s') AS formatted_date;
What will happen when this query runs?

medium

A. It will return NULL because the input has no time part.

B. It will cause a syntax error due to wrong format specifiers.

C. It will return '2024-06-15 00:00:00' as the time defaults to midnight.

D. It will return only the date part ignoring the time format.

Solution

1. Step 1: Analyze input and format

   The input date '2024-06-15' has no time, but MySQL treats missing time as '00:00:00'. Format includes time specifiers %H:%i:%s.

2. Step 2: Predict output behavior

   MySQL fills missing time with zeros, so output will be '2024-06-15 00:00:00'. No syntax error occurs.

3. Final Answer:

   It will return '2024-06-15 00:00:00' as the time defaults to midnight. -> Option C

4. Quick Check:

   Missing time defaults to 00:00:00 in DATE_FORMAT [OK]

Hint: Missing time defaults to 00:00:00, no error [OK]

Common Mistakes:

• Expecting syntax error for valid format
• Assuming NULL output for missing time
• Ignoring default midnight time fill

5. You want to display a datetime column created_at as 'Day-Month-Year Hour:Minute AM/PM' in a report. Which DATE_FORMAT pattern correctly formats it as '15-Jun-2024 02:30 PM'?

hard

A. DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p')

B. DATE_FORMAT(created_at, '%d-%m-%Y %H:%i %p')

C. DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %P')

D. DATE_FORMAT(created_at, '%d-%b-%Y %H:%i %p')

Solution

1. Step 1: Match each format code to desired output

   %d = day with leading zero (15), %b = abbreviated month name (Jun), %Y = 4-digit year (2024), %h = 12-hour hour (02), %i = minutes (30), %p = AM/PM uppercase.

2. Step 2: Check options for correct codes and case

   DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p') matches all codes correctly. DATE_FORMAT(created_at, '%d-%m-%Y %H:%i %p') uses %m (numeric month) instead of %b. DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %P') uses %M (full month) and %P (invalid). DATE_FORMAT(created_at, '%d-%b-%Y %H:%i %p') uses %H (24-hour) instead of %h.

3. Final Answer:

   DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p') -> Option A

4. Quick Check:

   Use %b for short month, %h for 12-hour, %p for AM/PM [OK]

Hint: Use %b for short month, %h for 12-hour, %p for AM/PM [OK]

Common Mistakes:

• Using %m instead of %b for month name
• Using %H (24-hour) instead of %h (12-hour)
• Using %P which is invalid in MySQL