How to Convert From Datetime Type to Date Only in MySQL
-
Convert From
DATETIMEType toDATEType in MySQL -
Use
DATE()to ConvertDATETIMEtoDATEin MySQL -
Use
CAST()to ConvertDATETIMEtoDATEin MySQL -
Use
CONVERT()to ConvertDATETIMEtoDATEin MySQL -
Use
DATE_FORMAT()to ConvertDATETIMEtoDATEin MySQL
Today, we will learn about the DATE(), CAST(), CONVERT(), and DATE_FORMAT() methods to convert from DATETIME type to DATE type in MySQL. The methods mentioned above are available and can be used in MySQL version 4.0 and above.
Convert From DATETIME Type to DATE Type in MySQL
To understand it with code examples, let’s create an orders table with id, productName, orderNumber, and orderDateTime as attributes; we will also create a primary key using the id field. See the following queries.
Example 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 target depending on the project requirements.
Use DATE() to Convert DATETIME to DATE in MySQL
Example 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)
The DATE() function can pull the DATE part from the valid DATE or DATETIME type field. It returns NULL if an invalid expression is passed.
We can use this article if we also want to group data according to the DATE only.
Use CAST() to Convert DATETIME to DATE in MySQL
Example 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)
The CAST() method is used to cast (convert) the one data type’s value to another given data type.
Use CONVERT() to Convert DATETIME to DATE in MySQL
Example 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 use the CONVERT() method to transform the DATETIME type field to DATE only. It accepts two arguments - the first is the value that needs to be converted, and the second is the data type to which the given value will be converted.
Use DATE_FORMAT() to Convert DATETIME to DATE in MySQL
Example 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)
The DATE_FORMAT() function converts the DATETIME to DATE type and is useful for changing the current date format. For instance, if we want to display the DATE in d/m/Y format, we can do that as follows.
Example 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)
We can also transform it in the following format using the DATE_FORMAT() function if we want to see the month’s name instead of the month’s number.
Example 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)
