Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Trebz
Starting Member
7 Posts |
Posted - 2006-06-05 : 06:34:42
|
HiI've been asked this question at the weekend and was a bit annoyed that I couldn't work out an answer, so as usual i'm now determind to find out how to do it!In a table I have a StartDate and EndDate column.I want to select data from the table where the records span a given date range, which will be in months, so for example, to get data in the 2nd quarter I want to be able to sayselect * from table where "the startdate and enddate span months 4 or 5 or 6"Or perhaps more simply, If I have a StartDate of 01-Jan-2006 and an EndDate of 01-Aug-2006, how can I tell if March is covered by those two dates? Do I need to convert the range into numbers, like @Range='1,2,3,4,5,6,7,8' and then say WHERE '3' IN @Range maybe?I'm sure there must be a simple answer, i've searched these forums and come up with nothing so far.Any suggestions much appreciated! |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-05 : 06:56:03
|
"the startdate and enddate span months 4 or 5 or 6"Do you mean the number of months between startdate and enddate ?use dateadiff(month, startdate, enddate) KH |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-05 : 06:57:55
|
Select * from yourTable where yourDate between StartDate and EnddateMadhivananFailing to plan is Planning to fail |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-05 : 07:00:45
|
You can use something like this...--datadeclare @t table (StartDate datetime, EndDate datetime)insert @t select '20060101', '20060801' --covers all of Marchunion all select '20060101', '20060201' --does not cover Marchunion all select '20060101', '20060315' --covers some of March--calculationselect * from @t where '20060301' between StartDate and EndDate --1st March is covered select * from @t where StartDate <= '20060301' and EndDate >= '20060331' --all of March is coveredselect * from @t where StartDate <= '20060401' and EndDate >= '20060630' --March-June is covered If this isn't the kind of thing you wanted, please post some sample data, example inputs and the results you would want.Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|