Check partial date between date range

[chrissanderson]

This is my first post on here. Bit of a SQL n00b so please bear with me )

I have an events search that lets users search by month and year (day is too specific). The events have have both a start and end date that can span over several days, or at the most, years.

I'm trying to build a SQL query that only returns results where the partial search date (mm/yyyy) is between EventStartDate and EventEndDate.

So for example, if you search on 11/2005, an event starting 31/10/2005 and ending 01/03/2007 would be returned.

I've tried loads of ideas, wasted countless hours and really need some help. Any thoughts?

 

[Sashanan2]

Welcome to Tek-Tips!

A WHERE component like this one should do the trick:

WHERE EventStartDate < #[searchdate]# AND EventEndDate > #[searchdate]#


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[chrissanderson]

Oops, I forgot to mention I'm using MS SQL, in fact I've probably posted in the wrong forum.

The biggest headache is the partial date that I'm passing (searchdate). With my example that would give:

WHERE 31/10/2005 < 11/2005 AND 01/03/2007 > 11/2005

Could I do something like:

WHERE 31/10/2005 < %/11/2005 AND 01/03/2007 > %/11/2005