Extract date part using MySQL's DATE function

Use MySQL's DATE function to extract the date part

MySQL is a widely used relational database management system that provides a wealth of functions to support data processing and querying. Among them, the DATE function is a commonly used function used to extract the date part.

When we need to extract the date part from a date time field, we can use the DATE function. The syntax of the DATE function is as follows:

DATE(date_time)

Among them, date_time represents a date and time value. This function will return a value containing only the date part.

Below, we use a code example to demonstrate how to use the DATE function to extract the date part.

First, we create a table to store employee information. The structure of the table is as follows:

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  hire_date DATETIME
);

Next, we insert some employee records:

INSERT INTO employees (name, hire_date) VALUES
  ('张三', '2021-01-01 09:00:00'),
  ('李四', '2021-02-15 10:30:00'),
  ('王五', '2021-03-20 14:15:00');

Now, we can use the DATE function to extract the date part from the hire_date field. The query statement is as follows:

SELECT name, DATE(hire_date) AS hire_date FROM employees;

Running the above query statement will return the following results:

+--------+------------+
| name   | hire_date  |
+--------+------------+
| 张三   | 2021-01-01 |
| 李四   | 2021-02-15 |
| 王五   | 2021-03-20 |
+--------+------------+

As you can see, the query result only contains the date part, and the time part is ignored.

In addition to extracting the date part from the DATETIME field, the DATE function can also be used for other date type fields, such as DATE fields. The steps to extract the date portion using the DATE function are the same.

It should be noted that the DATE function returns a date type value, not a string. If you need to format it into a string, you can use the DATE_FORMAT function.

To sum up, the date part can be easily extracted using MySQL's DATE function. Whether extracting from DATETIME fields or other date type fields, it can be easily achieved using the DATE function. This is very useful in many scenarios, such as counting daily sales, calculating employee length of service, etc.

The above is the detailed content of Extract date part using MySQL's DATE function. For more information, please follow other related articles on the PHP Chinese website!