Introduction

In MySQL, a DATE value is stored as a real date, not as a formatted string such as DD/MM/YYYY. If you want 25/12/2026 in query output, the correct tool is DATE_FORMAT for display or STR_TO_DATE when parsing user input into a proper date.

Format a Date in a SELECT

Use DATE_FORMAT when the column already contains a valid DATE or DATETIME.

1CREATE TABLE orders (
2    id INT PRIMARY KEY,
3    order_date DATE NOT NULL
4);
5
6INSERT INTO orders (id, order_date)
7VALUES
8    (1, '2026-03-11'),
9    (2, '2026-12-25');
10
11SELECT
12    id,
13    DATE_FORMAT(order_date, '%d/%m/%Y') AS order_date_display
14FROM orders;

The format string means:

• '%d for day with leading zero'
• '%m for month with leading zero'
• '%Y for four-digit year'

This affects only the result set. The underlying column remains a real date value, which is what you want for sorting, filtering, and indexing.

Parse DD/MM/YYYY Input into a Date

If input arrives as a string such as 11/03/2026, convert it into a proper DATE with STR_TO_DATE.

INSERT INTO orders (id, order_date)
VALUES (3, STR_TO_DATE('11/03/2026', '%d/%m/%Y'));

That conversion should happen at ingestion time whenever possible. Storing raw formatted date strings is a design mistake because string dates are harder to validate and query.

Filtering and Sorting Correctly

A common mistake is to format the date too early and then try to sort or filter the formatted string. Format only for presentation. Keep comparisons on the native date column.

Correct:

1SELECT
2    id,
3    DATE_FORMAT(order_date, '%d/%m/%Y') AS order_date_display
4FROM orders
5WHERE order_date >= '2026-01-01'
6ORDER BY order_date;

Risky and slower:

SELECT *
FROM orders
WHERE DATE_FORMAT(order_date, '%d/%m/%Y') = '11/03/2026';

The second version applies a function to the column, which can prevent effective index use and turns a date comparison into string logic.

DATETIME Columns Work Too

If the column includes time, you can still format only the date portion or include time in the output.

1SELECT
2    DATE_FORMAT(created_at, '%d/%m/%Y') AS date_only,
3    DATE_FORMAT(created_at, '%d/%m/%Y %H:%i:%s') AS full_timestamp
4FROM audit_log;

That is useful for reports and exports, but the same rule applies: store native temporal data, then format in the query or application layer when needed.

Prefer Formatting at the Presentation Layer When Possible

Although MySQL can format output, many applications choose to return the raw date and format it in the API, template, or client code. That makes localization and display customization easier. Database formatting is most useful for ad hoc reports, exports, or SQL-only workflows.

If the same query feeds multiple consumers, returning the raw date may be the cleaner contract.

Common Pitfalls

• Expecting MySQL to store DATE values in DD/MM/YYYY format confuses storage with display. The database stores a real date, not a formatted string.
• Storing dates as VARCHAR because the input arrives as DD/MM/YYYY makes querying and validation harder. Convert to DATE when inserting.
• Filtering on DATE_FORMAT(column, ...) instead of the raw date column can hurt performance and index usage. Compare native dates directly.
• Mixing up %m and %i produces incorrect output because %m is month and %i is minutes. Check the format tokens carefully.
• Assuming formatted strings sort like dates is unsafe. Sort by the real date column, not the display string.

Summary

• Use DATE_FORMAT(date_col, '%d/%m/%Y') to display a MySQL date as DD/MM/YYYY.
• Use STR_TO_DATE to parse incoming strings into proper date values.
• Keep dates stored as native DATE or DATETIME columns.
• Filter and sort on the raw date column, then format only for output.
• Treat formatting as presentation logic, not as the storage model.