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!This is a multi-part message in MIME format.
--=_NextPart_000_081B_01C6A751.D81740F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 =3D '200608'
SELECT ID
, Start_Date
, End_Date
FROM @.Durations WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND ( =convert( 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...
> 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! > >
--=_NextPart_000_081B_01C6A751.D81740F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 =3D ='200608'
SELECT = ID , Start_Date , End_DateFROM =@.Durations WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND =( convert( varchar(6), End_Date, 112 ))
-- Arnie Rowland"To be =successful, your heart must accompany your knowledge."
"James"
--=_NextPart_000_081B_01C6A751.D81740F0--
No comments:
Post a Comment