Tuesday, March 20, 2012

Current month date query

Hi,

I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient)

What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.

Do you just need something like this:

SELECT *FROM EventTableWHERE EventStartDate <= @.EndOfMonthOR EventEndDate >= @.StartOfMonth

You'll need to pass in the Start and End of Month values, of course. HTH

|||

I assume that you would like to return all events which started or finished in selected month. Maybe you can use Query like this if year is not important.

DECLARE @.tcMonth int

SET @.tcMonth=10 -- events for October

Select *

from Events

where MONTH(StartDate)=@.tcMonth

OR MONTH(EndDate)=@.tcMonth

Thanks

JPazgier

|||

That's great.

I changed the Or to an And though. :-)

|||

If you change OR to AND in this query you will receive only events which started and ended in selected month, with OR you should receive also events which were started earlier and finished in selected month and events which were started in selected month and finished after it.

Thanks

JPazgier

No comments:

Post a Comment