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