How to Compare MySQL Timestamp Dates With the Date Parameter Only

1. DATE() vs. CAST() vs. CONVERT() in MySQL
2. Compare MySQL Timestamp Dates With the Date Parameter Only
3. Use DATE() to Compare MySQL Timestamp Dates With the Date Parameter Only
4. Use CAST() to Compare MySQL Timestamp Dates With the Date Parameter Only
5. Use CONVERT() to Compare MySQL Timestamp Dates With the Date Parameter Only
6. Use BETWEEN to Compare MySQL Timestamp Dates With the Date Parameter Only

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

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

The following is a brief introduction to each function. You can also find more examples for each by clicking here.

the DATE() Method in MySQL

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

It returns NULL if the passed argument belongs to other than the mentioned types. This function is available if you are using MySQL version 4.0 or above.

the CONVERT() Method in MySQL

The CONVERT() method converts the particular value into the specified data type. For instance, we can convert the value of the TIMESTAMP type to the DATE type.

It takes two parameters, and both are required. The first parameter is the value that is supposed to be converted, and the other is the data type on which the specified value would be converted.

It is available in MySQL version 4.0 and above.

the CAST() Method in MySQL

The CAST() method is similar to CONVERT(), explained above. We use it to convert the value of one data type to another.

For instance, when we convert the value of DateTime to the DATE type. Like the CONVERT() method, it also takes two values: the value to convert and the data type to which you want to convert.

We can use this function if we have MySQL version 4.0 or above.

Compare MySQL Timestamp Dates With the Date Parameter Only

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

You can also create this table using the following queries to follow this tutorial with us.

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 the records of a specific date; for instance, for the date 2005-06-03 only. We need to compare the date parameter with all the col_datetime column values of the TIMESTAMP type.

Use DATE() to Compare MySQL Timestamp Dates With the Date Parameter Only

So, we can compare the timestamp date with the date parameter only 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 focus on the date only, we can convert the col_datetime to date using the DATE() 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 MySQL Timestamp Dates With the Date Parameter Only

We can also employ the CAST() function to compare the timestamp dates with only the 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 MySQL Timestamp Dates With the Date Parameter Only

The CONVERT() function can also compare the timestamp with the date only.

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 MySQL Timestamp Dates With the Date Parameter Only

The following solution will be the fastest if you have an index on the col_datetime column (with the TIMESTAMP type) because it could 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)