Query for finding records between to date

[RFH Reseller: Rappie]

I can't get my head around this one. I have a database table with all events that are held in a city. On the website events are shown for each month. Al events have start date. If the event is held longer then one day there also will be a end date. In a few occasions though some events will start in the previous month and end in the next month.

For example start August 31 and end October 4th. Because the events is running trough out September it needs to be shown on the event page for September. But I am struggling to write the appropriate query to do this.

Does anyone have any pointers?

[Staff: Warren Ashcroft]

How about:

SELECT * FROM events WHERE (start_date >= [01/MONTH/YEAR] AND end_date <= [31/MONTH/YEAR]) OR (start_date <= [01/MONTH/YEAR] AND end_date <= [31/MONTH/YEAR]) OR (start_date >= [01/MONTH/YEAR] AND end_date >= [31/MONTH/YEAR])

(all events will need an end date, even if it is the same as the start date)

[Staff: Warren Ashcroft]

Just updated that query, not sure if it's right though - bit of a puzzler!

[RFH Reseller: doogleq]

The following is a bit shorter but should work too:

select	*
FROM	events
WHERE	start_date <= [31/MONTH/YEAR]
AND	end_date >= [01/MONTH/YEAR]

Like Warren said, you'd need an end date on every record, even if it's the same as the start date.

Hope this helps
Dan

[RFH Reseller: Wise Webs]

Or this one should allow for your end dates being NULL (empty)



Declare @MonthStart smalldatetime
Declare @MonthEnd smalldatetime

-- Set our month duration
set @MonthStart = cast('23 May 2007' as smalldatetime)
set @MonthEnd = cast('31 May 2007' as smalldatetime)

SELECT * FROM EventItem
WHERE (EventStart between @MonthStart and @MonthEnd and EventEnd IS NULL) -- NULL End Date shows its a one day event
OR (EventEnd >= @MonthStart and EventStart <= @MonthEnd and EventEnd IS NOT NULL) -- Multiple day events

[RFH Reseller: Rappie]

Thanks for all the help guys.

I'm using unix timestamps for the dates. So if an event only last one day the end date is will be 0.

I went with this query:

SELECT * FROM  events WHERE 
(FROM_UNIXTIME(unixdate) >= '2009-09-01' 
AND FROM_UNIXTIME(unixenddate)  <= '2009-09-30') 
OR
(FROM_UNIXTIME(unixdate) <= '2009-09-01' 
AND FROM_UNIXTIME(unixenddate)  >= '2009-09-01')