How to Truncate Date in MySQL

How do I truncate a DATETIME to DATE in MySQL?

Truncating a date in MySQL removes the time portion or lower-level units (hour, minute, second) from a DATETIME/TIMESTAMP value, returning only the desired higher-level component such as DATE, month, or year.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why would I truncate a DATETIME to DATE in MySQL?

Reporting, grouping, and partitioning often require dates without time. Truncating ensures that 2023-10-07 10:23 and 2023-10-07 18:55 become the same key (2023-10-07) for accurate counts and aggregates.

What is the simplest way to trim the time portion?

Use the built-in DATE() function: DATE(datetime_col).It strips hours, minutes, and seconds, returning a DATE value while keeping the original time zone intact.

Sample usage

SELECT DATE(order_date) AS order_day FROM Orders; produces 2023-10-07 even if order_date stored a full timestamp.

How can I truncate to month or year?

Apply DATE_FORMAT() with format specifiers, then cast back to DATE for math safety.

DATE_FORMAT(order_date,'%Y-%m-01') → first day of month.
DATE_FORMAT(order_date,'%Y-01-01') → first day of year.

Which syntax variants exist?

DATE() is fastest; CAST(datetime AS DATE) gives the same result.DATE_FORMAT() offers flexible patterns but returns VARCHAR unless wrapped in DATE() or STR_TO_DATE().

Does truncation affect indexes?

Applying a function to a column in WHERE or JOIN disables B-tree indexes on that column. Instead, compute the truncated value once and store it, or create a generated column indexed for look-ups.

Best practices for date truncation

• Prefer DATE() for daily roll-ups.
• Use generated columns for indexed searches.
• Store timestamps in UTC; present in local time.
• Avoid truncating in WHERE; compare with range bounds instead.

Why How to Truncate Date in MySQL is important

How to Truncate Date in MySQL Example Usage


-- Find average order value per day
SELECT DATE(order_date)          AS order_day,
       ROUND(AVG(total_amount),2) AS avg_order_value
FROM   Orders
GROUP  BY order_day
ORDER  BY order_day DESC;

How to Truncate Date in MySQL Syntax


DATE(datetime_expr)
CAST(datetime_expr AS DATE)
DATE_FORMAT(datetime_expr, '%Y-%m-%d')

-- Ecommerce examples
-- 1. Daily sales totals
SELECT DATE(order_date) AS order_day,
       COUNT(*)           AS orders,
       SUM(total_amount)  AS revenue
FROM   Orders
GROUP  BY order_day;

-- 2. Filter orders from a specific day using range
SELECT *
FROM   Orders
WHERE  order_date >= '2023-10-07'
  AND  order_date  < '2023-10-08';

-- 3. Monthly inventory snapshot
SELECT DATE_FORMAT(created_at,'%Y-%m-01') AS month_start,
       SUM(stock) AS units_on_hand
FROM   Products
GROUP  BY month_start;

Common Mistakes

Using DATE(order_date) in WHERE clauses. This disables indexes and slows scans. Fix by comparing raw column to a date range: order_date >= '2023-10-07' AND order_date < '2023-10-08'.
Assuming DATE_FORMAT returns DATE. It returns VARCHAR, which can break comparisons. Always wrap with STR_TO_DATE or cast back: DATE(STR_TO_DATE(...)) or CAST(... AS DATE).

Frequently Asked Questions (FAQs)

Is DATE() faster than CAST(datetime AS DATE)?

Yes. DATE() is a lightweight wrapper and slightly quicker, but both use the same internal routine. Choose DATE() for readability.

Can I truncate a TIMESTAMP column the same way?

Absolutely. DATE(timestamp_col) works identically because TIMESTAMP is a date-time type. The function ignores the time zone conversion layer and returns a pure DATE.

How do I keep the index but still query by date?

Create a generated column such as order_day DATE AS (DATE(order_date)) STORED, then add an index on it. Your queries can filter on order_day without touching order_date.