JIYIK CN >

In this article we will use the DATE(), CAST(), and CONVERT()functions to compare MySQL timestamp dates with only the date parameter.

DATE() vs. CAST() vs. CONVERT() in MySQL

Below is a brief description of each function. You can also find more examples of each by clicking here.

DATE() Method in MySQL

DATE()The method extracts the date portion from a timestamp or DateTime expression. It has only one parameter, which must be of type DATE, TIMESTAMP, or DATETIME.

If the passed argument is not of the above type, NULL is returned. This feature is available if you are using MySQL 4.0 or later.

CONVERT() Method in MySQL

CONVERT()Method converts a specific value to a specified data type. For example, we can convert a value of type TIMESTAMP to type DATE.

It has two parameters, both of which are required. The first parameter is the value to be converted, and the other is the data type to which the value should be converted.

It is available in MySQL 4.0 and above.

CAST() Method in MySQL

CAST()method is similar to what was explained above CONVERT(). We use it to convert a value of one data type to another data type.

For example, when we convert the value of DateTime to DATE type. Like CONVERT()the method, it also has two values: the value to be converted and the data type to be converted.

If we have MySQL 4.0 or above, we can use this feature.

Compare only MySQL timestamp dates to date parameters

To compare only MySQL timestamp dates with the date parameter, we have created a table called date_comparison with two attributes ID and COL_DATETIME. Here ID is of type int and COL_DATETIME is TIMESTAMP.

You can also create this table using the following query to follow along with this tutorial.

example:

# Create a table
CREATE TABLE date_comparison(
    ID INT NOT NULL AUTO_INCREMENT,
    COL_DATETIME TIMESTAMP NOT NULL,
    PRIMARY KEY (ID));

# Insert data
INSERT INTO date_comparison(col_datetime)
VALUES
('2001-11-15 09:50:00'),
('2006-08-09 04:30:00'),
('2001-11-15 23:30:00'),
('2005-06-03 06:22:11'),
('2004-01-01 21:42:17');

# Show all data
SELECT * FROM date_comparison;

Output:

+----+---------------------+
| ID | col_datetime        |
+----+---------------------+
|  1 | 2001-11-15 09:50:00 |
|  2 | 2006-08-09 04:30:00 |
|  3 | 2001-11-15 23:30:00 |
|  4 | 2005-06-03 06:22:11 |
|  5 | 2004-01-01 21:42:17 |
+----+---------------------+
5 rows in set (0.00 sec)

Now, we want to retrieve records for a specific date; for example, only for the date 2005-06-03. We need to compare the date parameter with all col_datetime column values ​​of the TIMESTAMP type.

Use DATE() to compare only MySQL timestamp dates to date parameters

So we can only compare the timestamp date with the date parameter as follows.

example:

SELECT * FROM date_comparison WHERE DATE(col_datetime) = '2005-06-03';

Output:

+----+---------------------+
| ID | col_datetime        |
+----+---------------------+
|  4 | 2005-06-03 06:22:11 |
+----+---------------------+
1 row in set (0.00 sec)

If we are only interested in the date, we can DATE()convert col_datetime to a date using the function.

example:

SELECT ID, DATE(col_datetime) FROM date_comparison
WHERE DATE(col_datetime) = '2005-06-03';

Output:

+----+--------------------+
| ID | DATE(col_datetime) |
+----+--------------------+
|  4 | 2005-06-03         |
+----+--------------------+
1 row in set (0.00 sec)

Use CAST() to compare only MySQL timestamp dates to date parameters

We can also use the CAST() function to compare a timestamp date with a date parameter.

example:

SELECT ID, CAST(col_datetime AS DATE) FROM date_comparison
WHERE CAST(col_datetime AS DATE) = '2005-06-03';

Output:

+----+----------------------------+
| ID | CAST(col_datetime AS DATE) |
+----+----------------------------+
|  4 | 2005-06-03                 |
+----+----------------------------+
1 row in set (0.00 sec)

Use CONVERT() to compare only MySQL timestamp dates to date parameters

CONVERT()Functions can also compare just the timestamp to the date.

example:

SELECT ID, CONVERT(col_datetime, DATE) FROM date_comparison
WHERE CONVERT(col_datetime, DATE) = '2005-06-03';

Output:

+----+-----------------------------+
| ID | CONVERT(col_datetime, DATE) |
+----+-----------------------------+
|  4 | 2005-06-03                  |
+----+-----------------------------+
1 row in set (0.00 sec)

Use BETWEEN to compare only MySQL timestamp dates to date parameters

If you have an index on the col_datetime column (which has a TIMESTAMP type), the following solution will be the fastest because it can use the index on the col_datetime column.

example:

SELECT * FROM date_comparison
WHERE col_datetime
BETWEEN '2005-06-03 00:00:00' AND '2005-06-03 23:59:59';

Output:

+----+---------------------+
| ID | col_datetime        |
+----+---------------------+
|  4 | 2005-06-03 06:22:11 |
+----+---------------------+
1 row in set (0.00 sec)