| Author |
 Topic  |
|
|
Trebz
Starting Member
7 Posts |
 Posted - 06/05/2006 : 06:34:42
|
Hi
I'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 say
select * 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)
Singapore
16746 Posts |
Posted - 06/05/2006 : 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
India
22460 Posts |
Posted - 06/05/2006 : 06:57:55
|
Select * from yourTable where yourDate between StartDate and Enddate
Madhivanan
Failing to plan is Planning to fail |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 06/05/2006 : 07:00:45
|
You can use something like this...
--data
declare @t table (StartDate datetime, EndDate datetime)
insert @t
select '20060101', '20060801' --covers all of March
union all select '20060101', '20060201' --does not cover March
union all select '20060101', '20060315' --covers some of March
--calculation
select * 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 covered
select * from @t where StartDate <= '20060401' and EndDate >= '20060630' --March-June is coveredIf this isn't the kind of thing you wanted, please post some sample data, example inputs and the results you would want.
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
| |
Topic |
|
|
|
Does StartDate and EndDate span specified month
