Introduction

MySQL DATETIME columns contain both date and time, but many reports and filters need only the date part. Extracting a date value is easy in SELECT output, while filtering requires care to keep queries index-friendly. The best solution depends on whether you are formatting output, aggregating by day, or filtering large tables.

Select the Date Portion in Query Output

For projection only, use DATE(datetime_column).

1SELECT
2  id,
3  created_at,
4  DATE(created_at) AS created_day
5FROM orders
6ORDER BY created_at DESC
7LIMIT 10;

This returns a date value, not text. It is usually the correct choice for API responses that represent day-level timestamps.

You can also use CAST:

SELECT
  CAST(created_at AS DATE) AS created_day
FROM orders;

Both are readable. Teams usually pick one style and keep it consistent.

DATE() Versus DATE_FORMAT()

Use DATE_FORMAT only when you need a specific string shape for presentation.

1SELECT
2  DATE(created_at) AS day_value,
3  DATE_FORMAT(created_at, '%Y-%m-%d') AS day_iso_text,
4  DATE_FORMAT(created_at, '%d/%m/%Y') AS day_eu_text
5FROM orders
6LIMIT 5;

A formatted string is useful for exports, but avoid converting to text too early in data pipelines because text values are less convenient for date arithmetic and comparisons.

Group by Day for Aggregation

Daily metrics are a frequent requirement. Group by extracted day value:

1SELECT
2  DATE(created_at) AS order_day,
3  COUNT(*) AS order_count,
4  SUM(total_amount) AS total_revenue
5FROM orders
6GROUP BY DATE(created_at)
7ORDER BY order_day DESC;

This is easy to read and works well for moderate table sizes. For very large workloads, consider pre-aggregated tables or generated columns.

Filter by Date Without Killing Index Usage

A common mistake is filtering with WHERE DATE(created_at) = '2026-03-01'. Wrapping indexed columns in functions can prevent efficient index range scans.

Less efficient pattern:

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-03-01';

Preferred pattern uses start-inclusive and end-exclusive boundaries:

1SELECT *
2FROM orders
3WHERE created_at >= '2026-03-01 00:00:00'
4  AND created_at <  '2026-03-02 00:00:00';

This pattern typically preserves index benefits on created_at.

Time Zone Correctness

If data is stored in UTC but business reporting is local-time based, convert timezone before extracting date values. Otherwise rows near midnight can appear on the wrong reporting day.

1SELECT
2  DATE(CONVERT_TZ(created_at, 'UTC', 'America/Toronto')) AS local_day,
3  COUNT(*) AS events
4FROM orders
5GROUP BY local_day
6ORDER BY local_day;

Ensure MySQL timezone tables are loaded in environments where CONVERT_TZ is used.

Generated Date Column for Heavy Day Queries

If day-based filters are frequent and table volume is high, a generated column can simplify queries and indexing.

ALTER TABLE orders
ADD COLUMN created_day DATE GENERATED ALWAYS AS (DATE(created_at)) STORED,
ADD INDEX idx_orders_created_day (created_day);

Then query directly:

SELECT *
FROM orders
WHERE created_day = '2026-03-01';

This improves readability and can help planner choices depending on data distribution and workload.

Application Layer Parameter Handling

Pass explicit day boundaries from the application instead of concatenating SQL strings. This reduces SQL injection risk and timezone bugs.

1# Python example using mysql-connector
2query = """
3SELECT id, created_at
4FROM orders
5WHERE created_at >= %s AND created_at < %s
6"""
7params = ("2026-03-01 00:00:00", "2026-03-02 00:00:00")

Parameterized queries also keep query plans more stable.

Common Pitfalls

• Filtering with DATE(column) in large tables and losing index efficiency.
• Converting date values to strings too early, then doing fragile text comparisons.
• Ignoring timezone conversion for local business reports.
• Grouping by day without validating day boundaries for UTC-stored data.
• Hardcoding date literals in code instead of passing safe bound parameters.

Summary

• Use DATE() or CAST(... AS DATE) to project date-only values.
• Reserve DATE_FORMAT for presentation text output.
• Use range predicates on the raw DATETIME column for performant filtering.
• Convert timezone before date extraction when reporting is local-time based.
• Add generated date columns and indexes for frequent day-level analytics.