DATE_FORMAT function in MySQL

Introduction

The DATE_FORMAT function helps you change how a date looks so it's easier to read or fits what you need.

When you want to show a date in a friendly way, like 'January 1, 2024' instead of '2024-01-01'.

When you need to get just the month or year from a full date.

When you want to combine date parts with text, like 'Year: 2024'.

When you want to format dates for reports or user displays.

When you want to compare dates in a specific format.

Syntax

MySQL

DATE_FORMAT(date, format)

The 'date' is the date or datetime value you want to format.

The 'format' is a string that tells how to show the date using special codes like %Y for year or %m for month.

Examples

Shows the date as '2024-06-15' (year-month-day).

MySQL

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

Shows the date as 'June 15, 2024' with full month name.

MySQL

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

Shows only the time part as '14:30' (24-hour format).

MySQL

SELECT DATE_FORMAT('2024-06-15 14:30:00', '%H:%i');

Sample Program

This query formats a date to show the weekday, full month, day, and year. It also formats the time in 12-hour format with AM/PM.

MySQL

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

Important Notes

Use %Y for 4-digit year, %y for 2-digit year.

Use %M for full month name, %m for 2-digit month number.

Use %d for day of the month with leading zero.

Use %H for 24-hour, %h or %I for 12-hour format, and %r for 12-hour time with AM/PM.

Summary

DATE_FORMAT changes how dates and times look using special codes.

It helps make dates easier to read or fit your needs.

You can format both date and time parts in many ways.

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