DATE_FORMAT
In this page:
Introduction to DATE_FORMAT
DATE_FORMAT lets you control exactly how a date value is displayed, using format specifiers to choose the order and style of the year, month, and day rather than relying on MySQL's raw default format.
Example: Introduction to DATE_FORMAT
SELECT DATE_FORMAT('2024-06-15', '%d-%m-%Y') AS formatted_date;Extracting Years and Months
You can pull out just a piece of a date as readable text, such as the month name alone, which is useful for generating labels on charts or reports without exposing the full date value.
Example: Extracting Years and Months
SELECT DATE_FORMAT('2024-06-15', '%M') AS month_name;Formatting Time Values
Time components can be formatted the same way as dates, letting you choose between a 12-hour clock with AM/PM or a 24-hour format depending on what your audience expects to see.
Example: Formatting Time Values
SELECT DATE_FORMAT('2024-06-15 14:30:00', '%h:%i %p') AS twelve_hour;
SELECT DATE_FORMAT('2024-06-15 14:30:00', '%H:%i') AS twenty_four_hour;Custom Spacing and Slashes
Literal characters like slashes, dashes, or words can be mixed directly into the format string, and MySQL will insert them exactly as written around the extracted date parts to build a fully custom layout.
Example: Custom Spacing and Slashes
SELECT DATE_FORMAT('2024-06-15', '%d/%m/%Y') AS slashed_date;Formatting Table Dates
Applying DATE_FORMAT inside a SELECT statement formats the display for the user while leaving the actual stored date value completely untouched, so sorting and filtering still work against the real underlying date.
Example: Formatting Table Dates
CREATE TABLE orders (id INT, order_date DATE);
INSERT INTO orders VALUES (1, '2024-06-15');
SELECT id, DATE_FORMAT(order_date, '%d %M %Y') AS display_date FROM orders ORDER BY order_date;Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: