MYSQL search between two date

Question

I have 2 dates in database like this:

from_dateTime = 2013-06-12
to_dateTime   = 2013-07-10

I want to search all records between from_dateTime and to_dateTime. I tried to use following code in mysql:

SELECT *
FROM users i
WHERE i.from_dateTime >= '2013-06-12' AND i.to_dateTime <= '2013-07-10'

But it doesn't work as I expected. Why am I getting empty result set?

UPDATE:

from_dateTime = 2013-06-11
to_dateTime   = 2013-06-12

SQL:

SELECT * FROM users i WHERE  NOW() between i.from_dateTime AND i.to_dateTime

NOW() is 2013-06-12 08:17:13 and command cant find my record too.

is the date in database are in date format or string format?? — NULL, Jun 12 '13 at 04:46
@Harjeet Jadeja , i dont get error. this command could not find record between dates — , Jun 12 '13 at 04:48
@mahdipishguy there is no record then. Which record you want to be in result set? — BlitZ, Jun 12 '13 at 04:49
@Orangepill, if topic is between dates must be view and find with command — , Jun 12 '13 at 04:52
i hope this will help you.... http://stackoverflow.com/questions/3651985/compare-dates-in-mysql — NULL, Jun 12 '13 at 04:53
@mahdipishguy your query works just fine for me given you have the correct dates in place http://sqlfiddle.com/#!2/aea778/1/0 — Prix, Jun 12 '13 at 04:57

score 3 · Accepted Answer · answered Jun 12 '13 at 04:51

3

The problem is that you are comparing dates and datetimes. Dates start at midnight. Any time component is larger than the corresponding date. Try this:

SELECT *
FROM users u
WHERE date(NOW()) between u.from_dateTime AND u.to_dateTime

answered Jun 12 '13 at 04:51

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 12 '13 at 04:55

1

SELECT * 
FROM users i 
WHERE cast(NOW() as date) 
      between i.from_dateTime AND i.to_dateTime

SQL FIDDLE

answered Jun 12 '13 at 04:55

Prahalad Gaggar

11,389
16
53
71

score 0 · Answer 3 · edited Jun 12 '13 at 05:00

0

select from table_name where datetime_column >= '2013-06-11' and datetime_column <= '2013-06-12'

edited Jun 12 '13 at 05:00

Prahalad Gaggar

11,389
16
53
71

answered Jun 12 '13 at 04:55

Jayram

18,820
6
51
68

score 0 · Answer 4 · edited Jun 12 '13 at 04:59

0

Use between

SELECT * FROM users u where date(NOW()) between u.from_dateTime and u.to_dateTime

edited Jun 12 '13 at 04:59

Prahalad Gaggar

11,389
16
53
71

answered Jun 12 '13 at 04:57

Bhuvnesh Gupta

197
2
12

MYSQL search between two date

4 Answers4