Syntax for BETWEEN two dates

Forum Moderators: open

Message Too Old, No Replies

Syntax for BETWEEN two dates

Newbie question . . .

Wonderstuff

8:44 pm on Jun 1, 2007 (gmt 0)

Any assistance please. I must be close!

I have a table which contains:

An event e.g. A Circus
datefrom e.g. 2007-03-01
dateto e.g. 2007-05-31

If I want to call events by month e.g. April datefrom / dateto I have problems with:

SELECT * FROM table WHERE datefrom>='2007-04-01' OR dateto<='2007-04-30';

Nothing is selected because 04/April is BETWEEN the datefrom and dateto.

How would I rewrite the query using BETWEEN?

Many thanks W.

LifeinAsia

9:28 pm on Jun 1, 2007 (gmt 0)

Try this:
SELECT *
FROM table
WHERE datefrom<='2007-04-30' AND dateto>='2007-04-1'

syber

11:29 pm on Jun 1, 2007 (gmt 0)

SELECT *
FROM table
WHERE datefrom BETWEEN '2007-04-01' AND '2007-04-30';

LifeinAsia

11:34 pm on Jun 1, 2007 (gmt 0)

Syber,
Look at the example data- it won't be selected.

syber

12:09 am on Jun 2, 2007 (gmt 0)

True, but it is still the right answer.

If he doesn't want April dates then he would write:

SELECT *
FROM table
WHERE datefrom NOT BETWEEN '2007-04-01' AND '2007-04-30';

phranque

12:36 am on Jun 2, 2007 (gmt 0)

try this:

SELECT *
FROM table
WHERE datefrom BETWEEN '2007-04-01' AND '2007-04-30'
OR dateto BETWEEN '2007-04-01' AND '2007-04-30';

phranque

12:38 am on Jun 2, 2007 (gmt 0)

[edit]delete cat-submitted repeat...[/edit]

mattur

10:12 am on Jun 2, 2007 (gmt 0)

Or, to avoid messing about with dates:


SELECT *
FROM table
WHERE (MONTH(datefrom) = 4 AND YEAR(datefrom) = 2007)
OR (MONTH(dateto) = 4 AND YEAR(dateto) = 2007);

Wonderstuff

12:32 pm on Jun 2, 2007 (gmt 0)

Thanks to everyone that has made suggestions. Your help is really appreciated.

I think that the trick here is to SELECT APRIL/04 events without using APRIL/04 in the code. For example, if a different event(let's say a widget exhibition) ran from 2007-01-01 until 2007-08-31 it would be available in APRIL/04 as well as the reamining seven months.

So the winner is . . . .Lifeinasia.

Thanks again to everyone. The thinking behind your suggestions has been an inspiration.

LifeinAsia

4:07 pm on Jun 4, 2007 (gmt 0)

Whoo-hoo!