Welcome to WebmasterWorld Guest from 3.227.3.146

Forum Moderators: open

Message Too Old, No Replies

Syntax for BETWEEN two dates

Newbie question . . .

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

Junior Member

10+ Year Member

joined:May 21, 2005
posts:130
votes: 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.

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

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5853
votes: 200


Try this:
SELECT *
FROM table
WHERE datefrom<='2007-04-30' AND dateto>='2007-04-1'
11:29 pm on June 1, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


SELECT *
FROM table
WHERE datefrom BETWEEN '2007-04-01' AND '2007-04-30';
11:34 pm on June 1, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5853
votes: 200


Syber,
Look at the example data- it won't be selected.
12:09 am on June 2, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 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';

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

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11874
votes: 245


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';
12:38 am on June 2, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11874
votes: 245


[edit]delete cat-submitted repeat...[/edit]
10:12 am on June 2, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 29, 2002
posts:981
votes: 1


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);
12:32 pm on June 2, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:May 21, 2005
posts:130
votes: 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.

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

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5853
votes: 200


Whoo-hoo!