Return Only Sat/Sun dates in a date range

[dp Colgan]

Hi, I see alot of examples of returning the count of but was wondering how I return the actual dates of the weekends in a particular range. Can anyone provide an example.

[ck9663]

What do you have so far?

[clvandyke]

Please feel free to alter and use as needed

Here is a snipet of code I use to create a calendar table. I modified it to return only weekends between @StartDate and @EndDate

 IF (EXISTS(select * from tempdb..sysobjects where name LIKE '#Calendar%'))
	drop table #Calendar


create table #Calendar
 (
 DOW varchar(10),
 #CalendarDate datetime,
 Holiday Bit default 0,
 Workday Bit default 0,
 Weekend Bit default 0
 )
declare @StartDate date
declare @EndDate date
set @StartDate = '1/1/2011'
set @EndDate = '12/31/2012'

while @StartDate <= @EndDate
begin
Insert into #Calendar
(
 #CalendarDate
)
select
@StartDate
set @StartDate = dateadd (dd, 1, @StartDate)
end

-- Set the day of week name (DOW)
update 
#Calendar 
set
DOW = datename(dw,#CalendarDate)
go


-- Identify Weekends
SELECT
DATENAME(dw,#CalendarDate), #CalendarDate
FROM #Calendar
where datename(dw,#CalendarDate) in ('Saturday','Sunday')
go