Going slightly mad with querying date bands

Databases
1

Hi all.

Relatively new to SQL and can not get my head around my current problem.

I have a table containing price bands as follows :-

id day day2 rate
179 2012-06-02 2012-06-22 5.15
180 2012-06-23 2012-07-13 5.15
181 2012-07-14 2012-08-24 5.65
182 2012-08-25 2012-08-31 5.15

I simply need to fetch the applicable rows for any given date range

e.g. fetch the rows that apply between 2012-07-07 to 2012-07-14.

But because this date range is in the middle of row 2 I can not work out what the sql should be.
I have tried different combinations of >= and <= and the BETWEEN clause but to no avail.

My current query is :-

SELECT * FROM price
WHERE day BETWEEN ‘2012-07-07’ AND ‘2012-07-14’
AND unit_id = 2;

This brings back row 3 but I need the row before in order to get the band for July 7th.

Any one have any ideas?

Regards

Phil.

2

SELECT * FROM price
WHERE day <= ‘2012-07-14’
AND day2 >= ‘2012-07-07’
AND unit_id = 2;