Effective Date problem - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Effective Date problem

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2009-11-13 : 14:27:19

I am struggling with this Efective date range problem. hope someone will suggest something.

I have different contracts with start and end dates like

Contract Startdate Enddate
A 4/1/09 3/31/10
B 1/1/06 12/31/11
C 1/1/09 12/31/09

The end user should have a date range parameter where they can enter a specific date range and the contracts effective during that period only should popup.
Like for user date range effective 1/1/09 to 12/31/09 should list all 3 where as 1/1/10 to 12/31/10 should list only A and B.
please provide any suggestions to solve this issue.

Thanks,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-13 : 14:44:27

this??

select * from <table>
where datediff(dd,Startdate,@startdate) > 0
and datediff(dd,@enddate,Enddate) > 0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-11-13 : 14:52:17

create table #tmp (
Contract varchar(10),
Startdate datetime,
Enddate datetime
)

insert into #tmp
select 'A', '4/1/09', '3/31/10' union all
select 'B', '1/1/06', '12/31/11' union all
select 'C', '1/1/09', '12/31/09'

declare @sDate datetime
declare @eDate datetime

select 'Show Data', * from #tmp

select @sDate = '1/1/09', @eDate = '12/31/09'

select *
from #tmp
WHERE @sDate Between StartDate and EndDate OR @eDate Between StartDate and EndDate

select @sDate = '1/1/10', @eDate = '12/31/10'

select *
from #tmp
WHERE @sDate Between StartDate and EndDate OR @eDate Between StartDate and EndDate

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-13 : 14:53:19

select * from <table>
where Startdate >= @StartDate
and EndDate >= @EndDate

this would allow you to use indexes on the columns, if any exist.

Jim

Jim

Everyday I learn something that somebody else already knew

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2009-11-13 : 15:13:59

Thanks Vijay; I had to change the <=0 on one of them but it did worked.
It never hit me that datediff can be used for this..
Thanks again.

quote:
Originally posted by vijayisonly

this??

select * from <table>
where datediff(dd,Startdate,@startdate) > 0
and datediff(dd,@enddate,Enddate) > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources