Efficient Date Query?

Have data stored in two separate fields (Start_Date and End_Date). i.e.:
ID Start_Date End_Date
1 5/5/2006 7/8/2006
2 7/7/2006 7/9/2006
3 6/8/2006 8/5/2006
....
Basically I have to query this based on a MONTH and a YEAR and return all
records where any day in that month will fall between the start/end date.
So if I pass 7/2006 to the query, it will return all of the records. If I
choose 8/2006 it will only return the last one. If I choose 6/2006 it will
return the first and last. Theoretically, I could do a UNION of between
statements for every day a given month, but that is ridiculously ineffecient
and borderline stupid. There has to be a good way of doing this, I just
can't think of it off the top of my head and the guy who's paid to do so
isn't here. Can someon refresh my memory on an easy way to do this?
Thank you!The guy that's paid to do this isn't here either...
This may work for you. (I've selected using the ISO format for date.)
DECLARE @.MyDate varchar(6)
DECLARE @.Durations table
( ID int
, Start_Date datetime
, End_Date datetime
)
INSERT INTO @.Durations Values (1, '5/5/2006','7/8/2006')
INSERT INTO @.Durations Values (2, '7/7/2006','7/9/2006')
INSERT INTO @.Durations Values (3, '6/8/2006','8/5/2006')
SET @.MyDate = '200608'
SELECT
ID
, Start_Date
, End_Date
FROM @.Durations
WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND ( conver
t( varchar(6), End_Date, 112 ))
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"James" <minorkeys@.gmail.com> wrote in message news:uP8Lwh4pGHA.516@.TK2MSFTNGP05.phx.gbl...[
vbcol=seagreen]
> Have data stored in two separate fields (Start_Date and End_Date). i.e.:
>
> ID Start_Date End_Date
> 1 5/5/2006 7/8/2006
> 2 7/7/2006 7/9/2006
> 3 6/8/2006 8/5/2006
> ....
>
> Basically I have to query this based on a MONTH and a YEAR and return all
> records where any day in that month will fall between the start/end date.
> So if I pass 7/2006 to the query, it will return all of the records. If I
> choose 8/2006 it will only return the last one. If I choose 6/2006 it wil
l
> return the first and last. Theoretically, I could do a UNION of between
> statements for every day a given month, but that is ridiculously ineffecie
nt
> and borderline stupid. There has to be a good way of doing this, I just
> can't think of it off the top of my head and the guy who's paid to do so
> isn't here. Can someon refresh my memory on an easy way to do this?
>
> Thank you!
>
>[/vbcol]