I am working on a hotel booking engine. There is a special price scenario where the customer will see a special price based on the dates the admin defines. If the date the customer is booking a hotel room falls on the date range specified, the price they will see is what the admin has defined. So if the admin defines prices for a date rage from 1st Jan to 31st Jan in the following way, 1st Jan 2011 to 15th Jan 2011 = $100 / per day 16th Jan 2011 to 16th Jan 2011 = $500 / per day 17th Jan 2011 to 31st Jan 2011 = $100 / per day Code: CREATE TABLE `special_prices` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `date_start` DATE DEFAULT NULL, `date_end` DATE DEFAULT NULL, `special_price` DECIMAL(10,2) DEFAULT NULL, `status_id` TINYINT(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MYISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-1','2011-01-15','100','1'); INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-16','2011-01-16','500','1'); INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES (NULL,'2011-01-17','2011-01-31','100','1'); When I run the following query: Code: SELECT `id`, `date_start`, `date_end`, `special_price` FROM `special_prices` WHERE date_start >= '2011-01-12' AND date_end <= '2011-01-18' The result I get: Code: id date_start date_end special_price 2 2011-01-16 2011-01-16 500.00 Ideally the query should fetch all the 3 rows since they meet my query. Does anyone of you know why is this happening? Any help is appreciated.