When using MySQL, you can use the DATE() function to extract the date part from a date or datetime expression.

Here’s how it works.

Syntax

The syntax goes like this:

DATE(expr)

Where expr is the date or datetime expression.

Example 1 – Basic Usage

Here’s an example to demonstrate its basic usage.

SELECT DATE('2020-10-01 12:35:06') AS 'Result';

Result:

+------------+
| Result     |
+------------+
| 2020-10-01 |
+------------+

So I provided a value that included both the date and the time, and the DATE() function returned the date portion of that value.

Example 2 – Using it with NOW()

If you use a function like NOW() for example, you can use DATE() to extract just the date part from the return value of NOW().

SELECT 
    NOW(),
    DATE(NOW());

Result:

+---------------------+-------------+
| NOW()               | DATE(NOW()) |
+---------------------+-------------+
| 2018-06-25 08:33:42 | 2018-06-25  |
+---------------------+-------------+

However, in this case we could’ve just used CURDATE(). Doing this would eliminate the need to use DATE():

SELECT CURDATE();

Result:

+------------+
| CURDATE()  |
+------------+
| 2018-06-25 |
+------------+

Example 3 – A Database Query

Most of the time you’ll probably be using this function with values retrieved from a database. Such values may be stored as a datetime value and you just need the date part.

Here’s an example, where the database column is stored as a datetime value. I return that value, and I also use the DATE() function to return the date part from the same value:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DATE(payment_date) AS 'Date'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+------------+
| Date/Time           | Date       |
+---------------------+------------+
| 2005-05-25 11:30:37 | 2005-05-25 |
+---------------------+------------+