How to Format Dates in MariaDB

Galaxy Glossary

How do I format dates in MariaDB using DATE_FORMAT()?

DATE_FORMAT() converts a DATETIME or DATE column into a readable string according to a supplied format mask.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does DATE_FORMAT() do?

DATE_FORMAT() converts a DATE or DATETIME value into a string based on a format mask—similar to strftime in C. It is the go-to MariaDB function for presenting dates in reports, exports, and UI layers.

How is DATE_FORMAT() syntax structured?

The basic form is DATE_FORMAT(date_expression, format_mask).The first argument is any date/time expression; the second is a string containing format specifiers like %Y (4-digit year) or %d (zero-padded day).

Which format specifiers are most useful?

Common tokens include %Y = 2024, %y = 24, %m = 03, %d = 09, %H = 14, %i = 05, and %p = AM/PM. Combine them to build human-friendly strings.

How do I format order dates for invoices?

Use DATE_FORMAT(Orders.order_date,'%M %d, %Y') to get "March 09, 2024".Join this with customer data for mailing labels or PDFs.

Can I localize month names?

MariaDB inherits the server’s lc_time_names variable. SET it per connection—SET lc_time_names = 'fr_FR'—then DATE_FORMAT(order_date,'%e %M %Y') yields "9 mars 2024".

How do I remove leading zeros?

Switch from %d to %e (day) or from %m to %c (month). "March 9, 2024" reads cleaner than "March 09, 2024".

Best practice: store raw, format late

Keep columns as DATE/DATETIME.Apply DATE_FORMAT() only in SELECTs, views, or presentation layers. This preserves timezone math and indexing.

Best practice: index and filter on native dates

Filtering with WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' is Sargable. Avoid wrapping the column in DATE_FORMAT() inside WHERE, which forces full scans.

Why How to Format Dates in MariaDB is important

How to Format Dates in MariaDB Example Usage


SELECT
  C.name AS customer,
  DATE_FORMAT(O.order_date,'%M %d, %Y') AS invoice_date,
  O.total_amount
FROM Orders O
JOIN Customers C ON C.id = O.customer_id
WHERE O.order_date BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY O.order_date;

How to Format Dates in MariaDB Syntax


DATE_FORMAT(date_expression, format_mask)

-- Example: full invoice date
df := DATE_FORMAT(Orders.order_date,'%M %d, %Y')

-- Example: 24-hour timestamp
DATE_FORMAT(Orders.order_date,'%Y-%m-%d %H:%i:%s')

-- Example: compact key
DATE_FORMAT(Orders.order_date,'%Y%m%d')

-- Tables used
Customers(id, name, email, created_at)
Orders(id, customer_id, order_date, total_amount)
Products(id, name, price, stock)
OrderItems(id, order_id, product_id, quantity)

Common Mistakes

Using DATE_FORMAT() in WHERE clauses. This blocks index use because MariaDB must apply the function to every row. Filter on raw dates first, then format in the SELECT list.
Hard-coding locale strings. Relying on English month names breaks international installs. Set lc_time_names before calling DATE_FORMAT() or let the application layer translate.