Select All Dates Within a Range

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 2000 Forums

Transact-SQL (2000)

Select All Dates Within a Range

Author

Topic

BlackDog
Starting Member

18 Posts

Posted - 2005-01-21 : 14:28:41

Given a begin date and an end date, I need to select all dates that fall within that range. For example, there is a table with a BeginDate column and EndDate column. One row in that column has the values of '1/1/2005' for BeginDate and '1/5/2005' for EndDate.

I need a result set that lists:
1/1/2005
1/2/2005
1/3/2005
1/4/2005

Any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-21 : 15:16:28

BlackDog, something like this might do. (the @numbers table could be any existing table with an unbroken chain of integers):

declare @numbers table
(number int)

Declare @Dates Table
(FromDate datetime
,ToDate datetime)

set nocount on
insert @Dates
values ('1/1/2005', '1/5/2005')

--===================================
--add a bunch of numbers
declare @i int
set @i = 1
while @i < 1000
Begin
insert @numbers values (@i)
Select @i = @i + 1
End
--===================================

SElect convert(varChar(15), dateAdd(day, number-1, fromdate), 101)
from @dates
cross join @numbers
Where dateDiff(day,fromdate,todate) >= number

BlackDog
Starting Member

18 Posts

Posted - 2005-01-21 : 15:56:21

That might do rather well. Thanks! I did change the arbitrary 1000 rows to use the number of days between the begin and end date, but works good.

If any one else has another method, please post. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources