How to Remove the Time from a DATETIME Column
Remove the Time from a DATETIME Column, you will often encounter values stored as DATETIME, containing both a date and a time.
For example:
2024-05-19 23:10:04
Sometimes, however, you only need the date portion:
2024-05-19
MySQL provides the DATE() function to extract the date portion from a DATETIME or timestamp value.
The basic syntax is:
SELECTDATE(sales_time)FROM sales;
You can also give the resulting column a more descriptive name using AS:
SELECTDATE(sales_time) AS sales_dateFROM sales;
This is commonly described as truncating a datetime to a date because the time portion is removed from the value returned by the query.
Example: How to Truncate Dates in MySQL
Suppose we have a table named sales containing information about grocery store sales:
-- create tableCREATETABLE sales ( store_ID INTPRIMARYKEY, item TEXT NOTNULL, sales_time DATETIME NOTNULL);
We can insert some sample data:
INSERTINTO sales VALUES(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');
To view the complete table, use:
SELECT*FROM sales;
The output is:
+----------+---------+---------------------+| 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 |+----------+---------+---------------------+
Notice that the sales_time column contains both the date and time.
Suppose we only want the date portion of sales_time.
We can use:
SELECT store_ID, item, DATE(sales_time)FROM sales;
The result is:
+----------+---------+------------------+| 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 |+----------+---------+------------------+
The DATE() function removes the time portion from the value returned by the query.
Give the Truncated Date a Column Name
The default column name in the previous query is:
DATE(sales_time)
This isn’t particularly convenient when working with reports or downstream queries.
You can use the AS keyword to create a more meaningful column name:
SELECT store_ID, item,DATE(sales_time) AS sales_dateFROM sales;
The output becomes:
+----------+---------+------------+| 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 sales_date alias makes the query output easier to understand.
What Does DATE() Do in MySQL?
The DATE() function extracts the date portion of a date or datetime expression.
For example:
SELECTDATE('2024-08-17 15:30:45') AS date_value;Result:
+------------+| date_value |+------------+| 2024-08-17 |+------------+
The original value contains:
2024-08-17 15:30:45
After applying DATE(), only:
2024-08-17
is returned.
The hours, minutes, and seconds are excluded from the result.
Truncate DATETIME to DATE in a SELECT Statement
One of the most common use cases is converting a datetime column to a date while retrieving records.
For example:
SELECT store_ID, item, sales_time,DATE(sales_time) AS sales_dateFROM sales;
This allows you to display both values:
+----------+---------+---------------------+------------+| store_ID | item | sales_time | sales_date |+----------+---------+---------------------+------------+| 1 | Oranges | 2015-01-12 03:45:00 | 2015-01-12 || 2 | Apples | 2020-11-25 15:25:01 | 2020-11-25 || 3 | Bananas | 2009-06-30 09:01:39 | 2009-06-30 || 4 | Melons | 2022-04-09 03:29:55 | 2022-04-09 || 5 | Grapes | 2023-05-19 23:10:04 | 2023-05-19 |+----------+---------+---------------------+------------+
This can be useful when building reporting datasets where the original timestamp needs to be retained but a separate date field is also required.
Use DATE() to Group Sales by Day
A common reason for removing the time component is to aggregate data by day.
Suppose the sales table contains multiple transactions on the same date.
You can group them by date using:
SELECTDATE(sales_time) AS sales_date,COUNT(*) AS total_salesFROM salesGROUPBYDATE(sales_time)ORDERBY sales_date;
This treats all timestamps occurring on the same calendar date as one group.
For example, these values:
2024-05-19 09:15:002024-05-19 13:45:002024-05-19 18:20:00
all become:
2024-05-19
for grouping purposes.
Use DATE() to Calculate Daily Sales
If your table also contains a sales amount, you can calculate daily revenue:
SELECTDATE(sales_time) AS sales_date, SUM(amount) AS total_salesFROM salesGROUPBYDATE(sales_time)ORDERBY sales_date;
This is particularly useful for sales dashboards, business intelligence reports, financial analytics, and daily KPI reporting.
Filter Records by Date
You can also use DATE() when filtering a datetime column.
For example:
SELECT*FROM salesWHEREDATE(sales_time) ='2023-05-19';
This returns records that occurred on May 19, 2023, regardless of their time.
For example, all of these timestamps would match:
2023-05-19 08:15:002023-05-19 14:30:002023-05-19 23:10:04
because they all have the date:
2023-05-19
A More Efficient Way to Filter DATETIME by Date
Although DATE() is convenient, applying a function to a datetime column in a WHERE condition can prevent MySQL from efficiently using an index on that column in some situations.
For a large table, a range condition is often preferable.
Instead of:
SELECT*FROM salesWHEREDATE(sales_time) ='2023-05-19';
you can use:
SELECT*FROM salesWHERE sales_time >='2023-05-19 00:00:00'AND sales_time <'2023-05-20 00:00:00';
This approach is especially useful when sales_time has an index and the table contains millions of rows.
Extract the Year, Month, and Day Separately
The DATE() function returns the complete date, but MySQL also provides functions for extracting individual components.
To extract the year:
SELECTYEAR(sales_time) AS sales_yearFROM sales;
To extract the month:
SELECTMONTH(sales_time) AS sales_monthFROM sales;
To extract the day:
SELECTDAY(sales_time) AS sales_dayFROM sales;
You can also combine them:
SELECTYEAR(sales_time) AS sales_year,MONTH(sales_time) AS sales_month,DAY(sales_time) AS sales_dayFROM sales;
This can be useful when building date-based analytical features.
Remove the Time from a TIMESTAMP Column
The DATE() function can also be applied to a TIMESTAMP expression.
For example:
SELECTDATE(created_at) AS created_dateFROM orders;
If created_at contains:
2024-06-15 18:42:31
the result is:
2024-06-15
The important point is that DATE() returns the date portion of the expression.
DATE() Does Not Modify the Original Column
One important detail is that this query:
SELECTDATE(sales_time) AS sales_dateFROM sales;
does not change the data stored in sales_time.
The original column remains:
2023-05-19 23:10:04
The DATE() function only transforms the value in the query result.
If you actually want to change the stored data, you would need an UPDATE statement. However, removing time information permanently is usually something you should consider carefully because the original timestamp may be valuable for auditing and analytics.
DATE() vs DATE_FORMAT()
You can also use DATE_FORMAT() when you need a specific textual representation.
For example:
SELECT DATE_FORMAT(sales_time, '%Y-%m-%d') AS sales_dateFROM sales;
This produces:
2023-05-19
For simply extracting the date portion, DATE() is generally clearer:
SELECTDATE(sales_time) AS sales_dateFROM sales;
Use DATE_FORMAT() when you need a particular display format, such as:
SELECT DATE_FORMAT(sales_time, '%m/%d/%Y') AS sales_dateFROM sales;
which produces values such as:
05/19/2023
DATE() vs CAST()
Another option is to cast a DATETIME value as a DATE:
SELECTCAST(sales_time ASDATE) AS sales_dateFROM sales;
For example:
2023-05-19 23:10:04
becomes:
2023-05-19
Therefore, both of the following can be used to obtain a date:
SELECTDATE(sales_time)FROM sales;
and:
SELECTCAST(sales_time ASDATE)FROM sales;
For straightforward date extraction, DATE() is concise and easy to read.
Common Mistakes When Truncating Dates
Mistake 1: Using DATE_FORMAT() When You Need a Date
If you’re simply trying to remove the time portion, this is usually unnecessary:
DATE_FORMAT(sales_time, '%Y-%m-%d')
Instead, you can use:
DATE(sales_time)
Mistake 2: Assuming DATE() Changes the Table
This:
SELECTDATE(sales_time)FROM sales;
doesn’t permanently modify the sales_time column.
It only changes how the value is returned by the query.
Mistake 3: Using DATE() on an Indexed Column for Large-Scale Filtering
This:
WHEREDATE(sales_time) ='2023-05-19'
can be less efficient than using a datetime range when working with a large indexed table.
Consider:
WHERE sales_time >='2023-05-19 00:00:00'AND sales_time <'2023-05-20 00:00:00'
when query performance matters.
Complete MySQL Example
The following example can be copied and run directly in MySQL:
CREATETABLE sales ( store_ID INTPRIMARYKEY, item VARCHAR(50) NOTNULL, sales_time DATETIME NOTNULL);INSERTINTO sales VALUES(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');SELECT store_ID, item,DATE(sales_time) AS sales_dateFROM sales;
The final query produces:
+----------+---------+------------+| 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 |+----------+---------+------------+
Frequently Asked Questions
How do I remove the time from a datetime in MySQL?
Use the DATE() function:
SELECTDATE(sales_time) AS sales_dateFROM sales;
How do I convert DATETIME to DATE in MySQL?
You can use either:
SELECTDATE(sales_time)FROM sales;
or:
SELECTCAST(sales_time ASDATE)FROM sales;
Does DATE() delete the time from the database?
No. DATE() only extracts the date portion in the query result. It does not modify the original column.
How do I group MySQL records by date?
Use:
SELECTDATE(sales_time) AS sales_date,COUNT(*) AS total_salesFROM salesGROUPBYDATE(sales_time);
How do I filter a DATETIME column by a specific date?
For a simple query:
SELECT*FROM salesWHEREDATE(sales_time) ='2023-05-19';
For large indexed tables, a range condition can be more efficient:
SELECT*FROM salesWHERE sales_time >='2023-05-19 00:00:00'AND sales_time <'2023-05-20 00:00:00';
How do I truncate a datetime to the date in MySQL?
The simplest syntax is:
SELECTDATE(sales_time) AS sales_dateFROM sales;
Conclusion
The MySQL DATE() function provides a simple way to truncate a DATETIME value to its date portion.
The basic syntax is:
SELECTDATE(sales_time) AS sales_dateFROM sales;
For example, a value such as:
2023-05-19 23:10:04
is returned as:
2023-05-19
This technique is particularly useful for daily sales reports, date-based filtering, dashboard development, data analysis, SQL reporting, and business intelligence applications.
For simple extraction, DATE() is usually the clearest option. When filtering large indexed datetime columns, however, consider using a date range instead of wrapping the column in DATE() to give MySQL a better opportunity to use the index efficiently.
