When data needs to be filtered or sorted by date on the database side, MySQL has its own date-format syntax — different from PHP's. Knowing the conversion is essential when writing custom queries, reports or query-builder formulas that operate on dates.
MySQL Format Specifiers
%Y— 4-digit year (2026).%y— 2-digit year (26).%m— month (01-12).%c— month (1-12) without leading zero.%d— day of month (01-31).%e— day (1-31) without leading zero.%H— hour (00-23).%h/%I— hour (01-12).%i— minute (00-59).%s— second.%p— AM/PM.%W— weekday name (Monday).%a— abbreviated weekday (Mon).%M— month name (January).%b— abbreviated month (Jan).
Common Patterns
DATE_FORMAT(created_at, '%Y-%m-%d')— ISO date.DATE_FORMAT(created_at, '%d/%m/%Y')— UK short date.DATE_FORMAT(created_at, '%d %M %Y')— UK long date.DATE_FORMAT(created_at, '%Y-%m')— year-month for grouping.DATE_FORMAT(created_at, '%Y-W%u')— ISO week.
Where You'll Use This
- Custom report SQL.
- Query Builder advanced filters.
- Database views.
- Bulk update scripts.
- Migration / backfill scripts.
Worked Examples
- Monthly revenue report:
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) FROM orders GROUP BY month. - Weekly cohort analysis: Group by
DATE_FORMAT(signup_date, '%Y-W%u'). - Day-of-week breakdown:
DATE_FORMAT(created_at, '%W')for grouping by Monday, Tuesday, etc. - Date range conversion:
STR_TO_DATE('13/05/2026', '%d/%m/%Y')to parse a UK-formatted string into a DATE.