Declare @startdate datetime, @Enddate datetime
select @StartDate = '6/03/2007'
	,@EndDate = '6/12/2007'
	
	select @startdate , @Enddate , datename(weekday, @startdate ) 
	
Declare @t table (col1 datetime, col2 varchar(30))
While @startdate <= @Enddate 
	begin
		insert into @t 
		select @startdate , datename(weekday, @startdate ) 
		Set @startdate = Dateadd(dd, 1, @startdate )
	end
	
	select * from @t Where col2 not in ('Sunday', 'Saturday')

quote:

---

Originally posted by jsmith8858

I would simply create a calendar table, mark weekend days as such, and also mark holidays and anything else you need. Create it once with a loop, make it all indexed and nice, go through and update holidays (or create a quick and easy front end for someone else to maintain it) and then it is a simple SELECT with a simple WHERE clause to get whatever days you need. Calendar tables make things so much easier and quicker.

- Jeff
http://weblogs.sqlteam.com/JeffS

---

declare @StartDate datetime,@EndDate datetime
set @StartDate = '12/01/2006'
set @EndDate = '6/01/2007'

Declare @HolidayTable table(Date datetime)

Insert into @HolidayTable(Date)
select('12/24/2006') --xmas eve
Union all
select('12/25/2006') --xmas


begin
with TB1(d) as
(
	Select @StartDate
	Union all
	Select Dateadd(dd,1,d)
	from TB1 a
	where a.d <= @EndDate
	)
	select * 
	from TB1 a
	where not exists (Select Date from @Holidaytable aa where aa.Date = a.d)		
	and Datepart(dw,a.D) between 2 and 6
	OPTION (MAXRECURSION 1000)
end

quote:

---

Originally posted by Michael Valentine Jones

declare @start_date	datetime
declare @end_date	datetime

select @start_date	= '20070603'
select @end_date	= '20070612'

select
	[DATE]
from
	-- Function F_TABLE_DATE available here:
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
	dbo.F_TABLE_DATE ( @start_date, @end_date )
where
	[DAY_OF_WEEK] between 2 and 6
order by
	[DATE]

CODO ERGO SUM

---

quote:

---

Originally posted by Michael Valentine Jones

I agree that it would be better to have a permanent data table and the code I included is mostly to illustrate how it can be used.

However, function F_TABLE_DATE will probably perform OK for applications that only need to make occasional use of a Date table. I tested with the 2006-12-01 to 2007-06-12 date range and had an elapsed time of 17 milliseconds.



CODO ERGO SUM

---

quote:

---

Originally posted by jhermiz

quote:

---

Originally posted by Michael Valentine Jones

I agree that it would be better to have a permanent data table and the code I included is mostly to illustrate how it can be used.

However, function F_TABLE_DATE will probably perform OK for applications that only need to make occasional use of a Date table. I tested with the 2006-12-01 to 2007-06-12 date range and had an elapsed time of 17 milliseconds.



CODO ERGO SUM

---

Should work for me, thanks again folks.

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

---