between in mysql with dates gives incorrect result

[rashmisharmamca]

this is my table structure

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) DEFAULT NULL,
  `disable` timestamp NULL DEFAULT NULL,
  UNIQUE KEY `id` (`id`,`disable`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Assume values as

INSERT INTO `test` (`id`, `disable`) VALUES
(1, NULL),
(2, '2010-07-27 15:52:20'),
(3, '2010-08-03 00:00:00'),
(5, '2010-07-28 15:53:39'),
(6, '2010-07-30 15:53:39'),
(7, '2010-07-25 15:53:39');

now if i want the data between date 2010-07-27 and 2010-07-30, so my query is :
SELECT * FROM `test` WHERE disable >= '2010-07-27' and disable <='2010-07-30'
it gives result as

2 	2010-07-27 15:52:20
5 	2010-07-28 15:53:39

and if i query SELECT * FROM `test` WHERE disable between '2010-07-27' and '2010-07-30'
it gives the same result

but if i query for id between 2 and 6
SELECT * FROM `test` WHERE id between '2' and '6'
result is:

2 	2010-07-27 15:52:20
3 	2010-08-03 00:00:00
5 	2010-07-28 15:53:39
6 	2010-07-30 15:53:39

In second query it includes id 6 but with dates it excludes the last date(2010-07-30), I m not able to understand that behavior of mysql , i need the data from 2010-07-27 to 2020-07-30 including both dates. how to get that?

Plz help

Hi rashmisharmamca,

This is just a thought, I haven't tested it.
Because you don't specify any times in the querie, is it assuming the time is 00:00:00?
This would effectively make your querie
SELECT * FROM `test` WHERE disable >= '2010-07-27 00:00:00' and disable <='2010-07-30 00:00:00'

This querie will not include 2010-07-30 15:53:39

Hope this helps.
Zagga

[rashmisharmamca]

Thanks for the reply,I will try it.

[rashmisharmamca]

Thanks, it works. i need to specify time also for that query.