How do you truncate date in MySQL with an example?

---

You can use the following syntax in MySQL to truncate a date column:

SELECT store_ID, item, DATE(sales_time) FROM sales;

This particular example selects the store_ID column, the item column, and only the date portion of the datetime column named sales_time from the table named athletes.

By using the DATE statement, we are able to return only the date portion of the sales_time column without the time portion.

The following example shows how to use this syntax in practice.

Example: How to Truncate Dates in MySQL

Suppose we create the following table named sales that contains information about sales made at various grocery stores at various times:

-- create table 
CREATE TABLE sales (
  store_ID INT PRIMARY KEY,
  item TEXT NOT NULL,
  sales_time DATETIME NOT NULL
);

-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2015-01-12 03:45:00');
INSERT INTO sales VALUES (0002, 'Apples', '2020-11-25 15:25:01');
INSERT INTO sales VALUES (0003, 'Bananas', '2009-06-30 09:01:39');
INSERT INTO sales VALUES (0004, 'Melons', '2022-04-09 03:29:55');
INSERT INTO sales VALUES (0005, 'Grapes', '2023-05-19 23:10:04');

-- view all rows in table
SELECT * FROM sales;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_time          |
+----------+---------+---------------------+
|        1 | Oranges | 2015-01-12 03:45:00 |
|        2 | Apples  | 2020-11-25 15:25:01 |
|        3 | Bananas | 2009-06-30 09:01:39 |
|        4 | Melons  | 2022-04-09 03:29:55 |
|        5 | Grapes  | 2023-05-19 23:10:04 |
+----------+---------+---------------------+

We can use the following syntax to select the store_ID column, item column and only the date portion of the sales_time column:

SELECT store_ID, item, DATE(sales_time) FROM sales;

Output:

+----------+---------+------------------+
| store_ID | item    | DATE(sales_time) |
+----------+---------+------------------+
|        1 | Oranges | 2015-01-12       |
|        2 | Apples  | 2020-11-25       |
|        3 | Bananas | 2009-06-30       |
|        4 | Melons  | 2022-04-09       |
|        5 | Grapes  | 2023-05-19       |
+----------+---------+------------------+

Notice that only the date portion of the sales_time column is returned in the output, with the time portion truncated.

If you’d like, you can also use AS to assign an alias to the resulting truncated date column:

SELECT store_ID, item, DATE(sales_time) AS sales_date FROM sales;

+----------+---------+------------+
| store_ID | item    | sales_date |
+----------+---------+------------+
|        1 | Oranges | 2015-01-12 |
|        2 | Apples  | 2020-11-25 |
|        3 | Bananas | 2009-06-30 |
|        4 | Melons  | 2022-04-09 |
|        5 | Grapes  | 2023-05-19 |
+----------+---------+------------+

The name of the truncated date column is now sales_date, which is much easier to read than DATE(sales_time) from the previous example.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Truncate Strings
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest