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.