How to select date from datetime column?

Question

I have a column of type "datetime" with values like 2009-10-20 10:00:00

I would like to extract date from datetime and write a query like:

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

Is the following the best way to do it?

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

This however returns an empty resultset. Any suggestions?

Answer 1

Here are all formats

Say this is the column that contains the datetime value, table data.

+--------------------+
| date_created       |
+--------------------+
| 2018-06-02 15:50:30|
+--------------------+

---

mysql> select DATE(date_created) from data;
+--------------------+
| DATE(date_created) |
+--------------------+
| 2018-06-02         |
+--------------------+

---

mysql> select YEAR(date_created) from data;
+--------------------+
| YEAR(date_created) |
+--------------------+
|               2018 |
+--------------------+

---

mysql> select MONTH(date_created) from data;
+---------------------+
| MONTH(date_created) |
+---------------------+
|                   6 |
+---------------------+

---

mysql> select DAY(date_created) from data;
+-------------------+
| DAY(date_created) |
+-------------------+
|                 2 |
+-------------------+

---

mysql> select HOUR(date_created) from data;
+--------------------+
| HOUR(date_created) |
+--------------------+
|                 15 |
+--------------------+

---

mysql> select MINUTE(date_created) from data;
+----------------------+
| MINUTE(date_created) |
+----------------------+
|                   50 |
+----------------------+

---

mysql> select SECOND(date_created) from data;
+----------------------+
| SECOND(date_created) |
+----------------------+
|                   31 |
+----------------------+

Answer 2

You can use:

DATEDIFF ( day , startdate , enddate ) = 0

Or:

DATEPART( day, startdate ) = DATEPART(day, enddate)
AND 
DATEPART( month, startdate ) = DATEPART(month, enddate)
AND
DATEPART( year, startdate ) = DATEPART(year, enddate)

Or:

CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))

Answer 3

Well, using like in statement is the best option where datetime like '2009-10-20%' , it should work in this case

Answer 4

simple and best way to use date function

example

SELECT * FROM 
data 
WHERE date(datetime) = '2009-10-20' 

OR

SELECT * FROM 
data 
WHERE date(datetime ) >=   '2009-10-20'  && date(datetime )  <= '2009-10-20'

Answer 5

You can use MySQL's DATE() function:

WHERE DATE(datetime) = '2009-10-20'

You could also try this:

WHERE datetime LIKE '2009-10-20%'

See this answer for info on the performance implications of using LIKE.

Answer 6

Using WHERE DATE(datetime) = '2009-10-20' has performance issues. As stated here:

• it will calculate DATE() for all rows, including those, that don't match
• it will make it impossible to use an index for the query

Use BETWEEN or >, <, = operators which allow to use an index:

SELECT * FROM data 
WHERE datetime BETWEEN '2009-10-20 00:00:00' AND '2009-10-20 23:59:59'

---

Update: the impact on using LIKE instead of operators in an indexed column is high. These are some test results on a table with 1,176,000 rows:

• using datetime LIKE '2009-10-20%' => 2931ms
• using datetime >= '2009-10-20 00:00:00' AND datetime <= '2009-10-20 23:59:59' => 168ms

When doing a second call over the same query the difference is even higher: 2984ms vs 7ms (yes, just 7 milliseconds!). I found this while rewriting some old code on a project using Hibernate.