How to Extract the Date from a DateTime Column in MySQL?

Selecting Date from datetime Column

In a database table, you may have a "datetime" column that stores values with both date and time information. However, you may need to extract only the date component for specific queries.

To extract the date from a datetime column, you can use one of the following approaches:

Option 1: Using the DATE() Function

The MySQL DATE() function can be used to extract the date portion from a datetime value. For example:

SELECT * 
FROM data 
WHERE DATE(datetime) = '2009-10-20' 
ORDER BY datetime DESC

Option 2: Using the BETWEEN Range

You can also use the BETWEEN range operator to select rows where the datetime column falls within a specific date range. However, it's important to note that comparing datetime values directly (as in your example) may not produce the expected results. To correctly filter by date, you should specify a range that includes the desired day, for example:

SELECT * 
FROM data 
WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59')
ORDER BY datetime DESC

Option 3: Using the LIKE Operator

Another approach is to use the LIKE operator with a wildcard to match only the date portion. However, this is generally less efficient than using DATE() or BETWEEN. For example:

SELECT * 
FROM data 
WHERE datetime LIKE '2009-10-20%' 
ORDER BY datetime DESC

The above is the detailed content of How to Extract the Date from a DateTime Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!