JIYIK CN >

Today, we will learn the DATE(), CAST(), CONVERT() and DATE_FORMAT() methods to convert DATETIME type to DATE type in MySQL. The above mentioned methods can be used in MySQL 4.0 and above.

Converting from DATETIME to DATE in MySQL

To understand it through code examples, let us create an order table with id, productName, orderNumber, and orderDateTime as attributes; we will also create a primary key using the id field. See the following query.

Sample code:

CREATE TABLE orders(
    id INT NOT NULL,
    productName VARCHAR(50) NOT NULL,
    orderNumber INT NOT NULL,
    orderDateTime DATETIME NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO orders (id, productName, orderNumber, orderDateTime)
VALUES
(1, 'Oppo F17', 3322, '2022-04-11 04:32:15'),
(2, 'DELL Laptop', 5433, '2022-05-12 12:23:09'),
(3, 'HP Mouse', 3489, '2022-05-16 07:23:16');

SELECT * FROM orders;

Output:

+----+-------------+-------------+---------------------+
| id | productName | orderNumber | orderDateTime       |
+----+-------------+-------------+---------------------+
|  1 | Oppo F17    |        3322 | 2022-04-11 04:32:15 |
|  2 | DELL Laptop |        5433 | 2022-05-12 12:23:09 |
|  3 | HP Mouse    |        3489 | 2022-05-16 07:23:16 |
+----+-------------+-------------+---------------------+
3 rows in set (0.00 sec)

Once the table is ready, we can now use any of the following methods to meet the objectives as per the project requirements.

Convert DATETIME to DATE using DATE() in MySQL

Sample code:

SELECT id, productName, orderNumber, DATE(orderDateTime) FROM orders;

Output:

+----+-------------+-------------+---------------------+
| id | productName | orderNumber | DATE(orderDateTime) |
+----+-------------+-------------+---------------------+
|  1 | Oppo F17    |        3322 | 2022-04-11          |
|  2 | DELL Laptop |        5433 | 2022-05-12          |
|  3 | HP Mouse    |        3489 | 2022-05-16          |
+----+-------------+-------------+---------------------+
3 rows in set (0.00 sec)

DATE()The function can extract the DATE part from a valid DATE or DATETIME type field. If an invalid expression is passed, it will return NULL.

If we also want to group the data based on DATE only, we can use this post.

Convert DATETIME to DATE using CAST() in MySQL

Sample code:

SELECT id, productName, orderNumber, CAST(orderDateTime AS DATE) FROM orders;

Output:

+----+-------------+-------------+-----------------------------+
| id | productName | orderNumber | CAST(orderDateTime AS DATE) |
+----+-------------+-------------+-----------------------------+
|  1 | Oppo F17    |        3322 | 2022-04-11                  |
|  2 | DELL Laptop |        5433 | 2022-05-12                  |
|  3 | HP Mouse    |        3489 | 2022-05-16                  |
+----+-------------+-------------+-----------------------------+
3 rows in set (0.00 sec)

CAST()Methods are used to convert (cast) a value of one data type to another given data type.

Convert DATETIME to DATE using CONVERT() in MySQL

Sample code:

SELECT id, productName, orderNumber, CONVERT(orderDateTime, DATE) FROM orders;

Output:

+----+-------------+-------------+------------------------------+
| id | productName | orderNumber | CONVERT(orderDateTime, DATE) |
+----+-------------+-------------+------------------------------+
|  1 | Oppo F17    |        3322 | 2022-04-11                   |
|  2 | DELL Laptop |        5433 | 2022-05-12                   |
|  3 | HP Mouse    |        3489 | 2022-05-16                   |
+----+-------------+-------------+------------------------------+
3 rows in set (0.00 sec)

We can also CONVERT()convert a DATETIME type field to just DATE using the method. It accepts two parameters - the first is the value that needs to be converted and the second is the data type into which the given value will be converted.

Convert DATETIME to DATE using DATE_FORMAT() in MySQL

Sample code:

SELECT id, productName, orderNumber,
DATE_FORMAT(orderDateTime, '%Y-%m-%d') FROM orders;

Output:

+----+-------------+-------------+----------------------------------------+
| id | productName | orderNumber | DATE_FORMAT(orderDateTime, '%Y-%m-%d') |
+----+-------------+-------------+----------------------------------------+
|  1 | Oppo F17    |        3322 | 2022-04-11                             |
|  2 | DELL Laptop |        5433 | 2022-05-12                             |
|  3 | HP Mouse    |        3489 | 2022-05-16                             |
+----+-------------+-------------+----------------------------------------+
3 rows in set (0.00 sec)

DATE_FORMAT()The function converts DATETIME to DATE type and is useful for changing the current date format. For example, if we want to display DATE in d/m/Y format, we can do it as follows.

Sample code:

SELECT id, productName, orderNumber,
DATE_FORMAT(orderDateTime, '%d/%m/%Y') FROM orders;

Output:

+----+-------------+-------------+----------------------------------------+
| id | productName | orderNumber | DATE_FORMAT(orderDateTime, '%d/%m/%Y') |
+----+-------------+-------------+----------------------------------------+
|  1 | Oppo F17    |        3322 | 11/04/2022                             |
|  2 | DELL Laptop |        5433 | 12/05/2022                             |
|  3 | HP Mouse    |        3489 | 16/05/2022                             |
+----+-------------+-------------+----------------------------------------+
3 rows in set (0.00 sec)

If we want to see the name of the month instead of the number of the month, we can also use DATE_FORMAT()the function to convert it to the following format.

Sample code:

SELECT id, productName, orderNumber,
DATE_FORMAT(orderDateTime, '%D %M %Y') FROM orders;

Output:

+----+-------------+-------------+----------------------------------------+
| id | productName | orderNumber | DATE_FORMAT(orderDateTime, '%D %M %Y') |
+----+-------------+-------------+----------------------------------------+
|  1 | Oppo F17    |        3322 | 11th April 2022                        |
|  2 | DELL Laptop |        5433 | 12th May 2022                          |
|  3 | HP Mouse    |        3489 | 16th May 2022                          |
+----+-------------+-------------+----------------------------------------+
3 rows in set (0.00 sec)