MySQL search record in table by DATETIME

Question

I`ve created this table in MySQL:

CREATE TABLE events (
   id TINYINT PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR ( 15 ) NOT NULL,
   description TEXT NOT NULL,
  _type VARCHAR ( 15 ) NOT NULL,
  date_time DATETIME NOT NULL,
  location VARCHAR ( 15 ) NOT NULL
)ENGINE = INNODB;

Then i have inserted some records in the table,one of them has DATETIME field 2014-04-04 00:00:00. What i want is to search this record by it`s DATETIME field. For example:

SELECT * FROM events WHERE date_time = 2014-04-04;

But this query returns:

Empty set, 1 warning (0.00 sec)

Why this isn`t working?

score 1 · Accepted Answer · answered Apr 10 '14 at 14:11

1

You need to use DATE() to get the date portion of the datetime string for comparison (and put quotes around your date string):

SELECT * FROM events WHERE DATE(date_time) = '2014-04-04';

answered Apr 10 '14 at 14:11

John Conde

217,595
99
455
496

Hey Jon, can you comment on the accepted answer [here](http://stackoverflow.com/questions/14104304/mysql-select-where-datetime-matches-day-and-not-necessarily-time)? I was about to flag this as a dupe and it recommends against your answer of using `DATE` – Andy Apr 10 '14 at 14:12
Worked ! Sorry for the silly question and thanks.. :) – user2976091 Apr 10 '14 at 14:12
1

@Andy That would be a better practice then what I answered here. – John Conde Apr 10 '14 at 14:13

score 1 · Answer 2 · edited May 23 '17 at 11:49

An option you have is

WHERE columname BETWEEN '2014-04-04 00:00:00' AND '2014-04-04 23:59:59'

The advantage of using this over the WHERE DATE(date_time) = '2014-04-04' answer, is that when using the DATE function (as mentioned by Eugine Reick in this answer)

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

Thus, if your table is small and not expected to grow significantly, the DATE answer will work for you. However, if you are expecting a large volume of data, performance will deteriorate. It is better to use the BETWEEN option.

Yes, i`ve tried this and it worked. One more little question - how can i search only by day for example ? — user2976091, Apr 10 '14 at 14:43

MySQL search record in table by DATETIME

2 Answers2