Select all days between two dates

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 days between two dates

Author

Topic

thezo
Starting Member

4 Posts

Posted - 2005-12-01 : 20:02:15

Hi. I just found this forum today while googling for an answer for a query I am trying to write. Think I'll have to stick around. Maybe I'll even be able to contribute a little.

I am trying to write a query that will run all the days and the name of the day between two set dates.

Example:

D1 = 12/28/2005
D2 = 1/3/06

Results:

12/28/2005 Wednesday
12/29/2005 Thursday
12/30/2005 Friday
12/31/2005 Saturday
01/01/2006 Sunday
01/02/2006 Monday
01/03/2006 Tuesday

Any help is appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-01 : 20:36:45

It's fairly easy to do with a number table. The code below shows how.

It uses a function to generate a table of numbers. You can get the code for the F_TABLE_NUMBER_RANGE function on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


declare @start_date		datetime
declare @end_date		datetime
declare @days			int

select @start_date	= '2005/12/28'
select @end_date	= '2006/01/06'

select @days = datediff(dd,@start_date,@end_date) +1

select
	[Date]		= dateadd(dd,number-1,@start_date),
	[Day of Week]	= datename(weekday,dateadd(dd,number-1,@start_date))
from
	dbo.F_TABLE_NUMBER_RANGE( 1, @days )
order by
	number



Date                                                   Day of Week
------------------------------------------------------ -------------
2005-12-28 00:00:00.000                                Wednesday
2005-12-29 00:00:00.000                                Thursday
2005-12-30 00:00:00.000                                Friday
2005-12-31 00:00:00.000                                Saturday
2006-01-01 00:00:00.000                                Sunday
2006-01-02 00:00:00.000                                Monday
2006-01-03 00:00:00.000                                Tuesday
2006-01-04 00:00:00.000                                Wednesday
2006-01-05 00:00:00.000                                Thursday
2006-01-06 00:00:00.000                                Friday

(10 row(s) affected)

CODO ERGO SUM

thezo
Starting Member

4 Posts

Posted - 2005-12-01 : 20:51:57

Perfect. the F_Table_Number_Range is nice. I'll have to take a look at that some more.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 03:54:04

Other approach


Declare @date table(d datetime)
Declare @d datetime

set @d='20051228'

While @d<='20060106'
Begin
	Insert into @date values (@d)
	set @d=@d+1
End
Select d as DateCol,datename(weekday,dateadd(day,0,d)) as DayName from @date

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources