MySQL DATE() Function

Summary: in this tutorial, you will learn how to use the MySQL DATE() function to extract the date part of a datetime or timestamp value.

Introduction to MySQL DATE() function

The DATE() function allows you to extract the date component from a datetime or timestamp expression.

Here’s the syntax of the DATE() function:

DATE(expression)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression: This is an expression that evaluates to a DATE, a DATETIME, or a TIMESTAMP value from which you want to extract the value.

The DATE() function returns the DATE value. It returns NULL if the expression is NULL.

The DATE() function is a convenient tool for working with DATETIME and TIMESTAMP values in the database. It allows you to extract, filter, group, and perform date arithmetic on date expressions. By using the DATE() function in your queries, you can manage and analyze date-related data more effectively.

MySQL DATE() function examples

Let’s take some examples of using the MySQL DATE() function.

1) Extracting a date from a DATETIME value

The following example uses the DATE() function to extract the date from a DATETIME literal value:

SELECT DATE('2023-10-17 14:30:45');Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| DATE('2023-10-17 14:30:45') |
+-----------------------------+
| 2023-10-17                  |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the DATE() function returns a DATE value ('2023-10-17') from the DATETIME value '2023-10-17 14:30:45'.

2) Using the DATE() function with table data

First, create a new table called events with the following structure:

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time DATETIME,
    name VARCHAR(255)
);Code language: SQL (Structured Query Language) (sql)

The events table has three columns id, time, and name. The time column has the DATETIME type.

Second, insert four rows into the events table:

INSERT INTO events (time, name)
VALUES
    ('2023-10-15 09:00:00', 'Conference A'),
    ('2023-10-15 18:30:00', 'Conference B'),
    ('2023-10-16 14:00:00', 'Workshop X'),
    ('2023-10-16 19:00:00', 'Workshop Y');Code language: SQL (Structured Query Language) (sql)

Third, query data from the events table:

SELECT *
FROM events
WHERE DATE(time) = '2023-10-15';Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------------------+--------------+
| id | time                | name         |
+----+---------------------+--------------+
|  1 | 2023-10-15 09:00:00 | Conference A |
|  2 | 2023-10-15 18:30:00 | Conference B |
+----+---------------------+--------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we use the DATE() function to extract the date part of the time column and use its result to filter the events based on a specific date.

Summary

• Use the DATE() function to extract the date part of a datetime or timestamp value.