MySQL DATE_FORMAT and STR_TO_DATE: Format and Parse Dates

Dates are stored in one fixed format, but people want to see them in many. DATE_FORMAT converts a date to text with a pattern you choose. STR_TO_DATE goes the other way.

query.sql

SQL

SELECT DATE_FORMAT('2026-07-15 14:30:45', '%Y-%m-%d')            AS iso_date,
       DATE_FORMAT('2026-07-15 14:30:45', '%d/%m/%Y')            AS day_first,
       DATE_FORMAT('2026-07-15 14:30:45', '%d %b %Y')            AS short_month,
       DATE_FORMAT('2026-07-15 14:30:45', '%W, %M %e, %Y')       AS long_form,
       DATE_FORMAT('2026-07-15 14:30:45', '%h:%i %p')            AS twelve_hour,
       DATE_FORMAT('2026-07-15 14:30:45', '%H:%i:%s')            AS twenty_four_hour;

Output

OUTPUT

+------------+------------+-------------+--------------------------+-------------+------------------+
| iso_date   | day_first  | short_month | long_form                | twelve_hour | twenty_four_hour |
+------------+------------+-------------+--------------------------+-------------+------------------+
| 2026-07-15 | 15/07/2026 | 15 Jul 2026 | Wednesday, July 15, 2026 | 02:30 PM    | 14:30:45         |
+------------+------------+-------------+--------------------------+-------------+------------------+
1 row in set

The common specifiers

Code	Meaning	Example
`%Y`	Year, 4 digits	2026
`%y`	Year, 2 digits	26
`%m`	Month, 01 to 12	07
`%c`	Month, 1 to 12	7
`%M`	Month name	July
`%b`	Month abbreviation	Jul
`%d`	Day, 01 to 31	05
`%e`	Day, 1 to 31	5
`%W`	Weekday name	Wednesday
`%a`	Weekday abbreviation	Wed
`%H`	Hour, 00 to 23	14
`%h`	Hour, 01 to 12	02
`%i`	Minutes	30
`%s`	Seconds	45
`%p`	AM or PM	PM
`%j`	Day of the year	196
`%u`	Week of the year, Monday first (mode 1)	29
`%%`	A literal percent sign	%

%m is the month and %i is the minutes. Mixing them up (writing %m for minutes) is a classic mistake.

Grouping by month

DATE_FORMAT is handy for reports:

query.sql

SQL

CREATE TABLE sales (id INT AUTO_INCREMENT PRIMARY KEY, sold_on DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO sales (sold_on, amount) VALUES ('2026-05-03', 100), ('2026-05-20', 250), ('2026-06-11', 80), ('2026-06-12', 120), ('2026-07-01', 300);

SELECT DATE_FORMAT(sold_on, '%Y-%m') AS month, SUM(amount) AS revenue, COUNT(*) AS orders
FROM sales
GROUP BY DATE_FORMAT(sold_on, '%Y-%m')
ORDER BY month;

Output

OUTPUT

+---------+---------+--------+
| month   | revenue | orders |
+---------+---------+--------+
| 2026-05 |  350.00 |      2 |
| 2026-06 |  200.00 |      2 |
| 2026-07 |  300.00 |      1 |
+---------+---------+--------+
3 rows in set

%Y-%m sorts correctly as text, because the year comes first.

Names depend on the language setting

Month and day names come from the lc_time_names variable, which defaults to en_US:

query.sql

SQL

SET lc_time_names = 'de_DE';
SELECT DATE_FORMAT('2026-07-15', '%W %M') AS german_names;
SET lc_time_names = 'en_US';
SELECT DATE_FORMAT('2026-07-15', '%W %M') AS english_names;

Output

OUTPUT

+---------------+
| german_names  |
+---------------+
| Mittwoch Juli |
+---------------+
1 row in set

+----------------+
| english_names  |
+----------------+
| Wednesday July |
+----------------+
1 row in set

In most applications it is better to format dates in the application (your language and the user’s locale), and let MySQL return real dates.

STR_TO_DATE: text to date

query.sql

SQL

SELECT STR_TO_DATE('15/07/2026', '%d/%m/%Y')                   AS from_day_first,
       STR_TO_DATE('July 15, 2026', '%M %d, %Y')               AS from_long_form,
       STR_TO_DATE('2026-07-15 02:30 PM', '%Y-%m-%d %h:%i %p') AS with_time,
       STR_TO_DATE('15.07.26', '%d.%m.%y')                      AS two_digit_year,
       STR_TO_DATE('not a date', '%d/%m/%Y')                   AS invalid_gives_null;

Output

OUTPUT

+----------------+----------------+---------------------+----------------+--------------------+
| from_day_first | from_long_form | with_time           | two_digit_year | invalid_gives_null |
+----------------+----------------+---------------------+----------------+--------------------+
| 2026-07-15     | 2026-07-15     | 2026-07-15 14:30:00 | 2026-07-15     | NULL               |
+----------------+----------------+---------------------+----------------+--------------------+
1 row in set

Use STR_TO_DATE when you import dates in non-standard formats (Importing CSV Safely). An input that does not match the pattern gives NULL in a SELECT, and an error in an INSERT or UPDATE in strict mode.

Week numbers

Week numbering differs by country. WEEK(date, mode) takes a mode:

query.sql

SQL

SELECT WEEK('2026-01-01')    AS default_mode_0,
       WEEK('2026-01-01', 1) AS mode_1_monday_first,
       YEARWEEK('2026-01-01', 3) AS iso_year_week,
       WEEKOFYEAR('2026-01-01') AS iso_week;

Output

OUTPUT

+----------------+---------------------+---------------+----------+
| default_mode_0 | mode_1_monday_first | iso_year_week | iso_week |
+----------------+---------------------+---------------+----------+
|              0 |                   1 |        202601 |        1 |
+----------------+---------------------+---------------+----------+
1 row in set

For ISO weeks (used in Europe and in most business calendars), use WEEKOFYEAR or WEEK(date, 3), and YEARWEEK(date, 3) for the year that the week belongs to. The last days of December can belong to week 1 of the next year, and the first days of January to week 52 or 53 of the previous one.

Formatting is for display only

DATE_FORMAT returns text. Do not store it back as the date, do not use it to compare dates (DATE_FORMAT(d, '%d/%m/%Y') > ... sorts as text and uses no index), and do not apply it to a column in WHERE. Keep real dates in the database, and format at the last step.

lightbulb

Remember the three

DATE_FORMAT(date, pattern) makes text. STR_TO_DATE(text, pattern) makes a date. The patterns use the same codes in both.