How to use the DATE function to get the date part of a date value in MySQL

MySQL is a commonly used relational database management system, which provides a wealth of functions for data manipulation and processing. In the actual development process, it is often necessary to obtain the date part from a date type column in order to perform operations such as data analysis, statistics, and calculations. This article will explain how to use MySQL's DATE function to get the date part of a date value.

In MySQL, the DATE function can be used to extract the date part of a date type column. The syntax of the DATE function is as follows:

DATE(date)

Among them, the date parameter is a date value or date expression. The DATE function will return the date portion of the date parameter as a value of type Date.

The following is a specific example, assuming there is a table named orders, which has a column named order_date, which stores the date information of the order. We want to get the date part of the order date, which can be achieved by using the DATE function.

First, we need to create an orders table and insert some data into it. You can use the following SQL statement to complete related operations:

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE,
  order_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_date, order_amount) VALUES
  ('2022-01-01', 100.00),
  ('2022-02-01', 150.00),
  ('2022-03-05', 200.00),
  ('2022-04-10', 300.00);

The above SQL statement creates a table named orders and inserts four pieces of order data into it. Each order data includes the order date (order_date) and the order Amount (order_amount).

Next, we use the DATE function to get the date part of the order date. You can use the following SQL statement to complete related operations:

SELECT id, DATE(order_date) AS order_date FROM orders;

The above SQL statement uses the SELECT statement to query the id and order_date columns in the orders table, and uses the DATE function to extract the date part of the order_date column and name it. is order_date.

Execute the above SQL statement and you will get the following results:

+----+------------+
| id | order_date |
+----+------------+
|  1 | 2022-01-01 |
|  2 | 2022-02-01 |
|  3 | 2022-03-05 |
|  4 | 2022-04-10 |
+----+------------+

You can see that in the obtained results, the value of the order_date column has been intercepted into the date part, and the time part has been automatically set is 0.

In the above example, we used the DATE function to extract the date part and return it as a new column. In addition to the above methods, we can also use the DATE function directly in the WHERE condition or elsewhere to calculate and compare dates.

For example, we can use the following SQL statement to query orders with order dates greater than or equal to February 1, 2022 (inclusive):

SELECT * FROM orders WHERE DATE(order_date) >= '2022-02-01';

Execute the above SQL statement and you will get the following results:

+----+------------+---------------+
| id | order_date | order_amount  |
+----+------------+---------------+
|  2 | 2022-02-01 |         150.00 |
|  3 | 2022-03-05 |         200.00 |
|  4 | 2022-04-10 |         300.00 |
+----+------------+---------------+

In the above example, we used the DATE function to compare the order_date column and filter out orders with order dates greater than or equal to the specified date.

In summary, MySQL's DATE function provides a convenient way to obtain the date part of a date value. Whether you are performing data analysis, statistics or date comparison operations, you can use the DATE function to quickly and accurately obtain the date part and process it accordingly.

The above is the detailed content of How to use the DATE function to get the date part of a date value in MySQL. For more information, please follow other related articles on the PHP Chinese website!