通過SQL取出所有周六/周日的日期到Table

 

create table SatSun([id] int identity(1,1),[date] datetime,[weekday] char(6))
go

declare @datetime datetime,@weekday char(6)
set @datetime='2009-1-1'
while @datetime<='2009-12-31'
begin
select @weekday=datename(weekday,@datetime)
if @weekday= '星期六' insert SatSun([date],[weekday]) values(@datetime,@weekday)
if @weekday='星期日' insert SatSun([date],[weekday]) values(@datetime,'星期天')
select @datetime=dateadd(day,1,@datetime)
end
go

select * from satsun

drop table satsun

/*
id          date                                                   weekday
----------- ------------------------------------------------------ -------
1           2009-01-06 00:00:00.000                                星期六
2           2009-01-07 00:00:00.000                                星期天
3           2009-01-13 00:00:00.000                                星期六
4           2009-01-14 00:00:00.000                                星期天
5           2009-01-20 00:00:00.000                                星期六
6           2009-01-21 00:00:00.000                                星期天
7           2009-01-27 00:00:00.000                                星期六
8           2009-01-28 00:00:00.000                                星期天
9           2009-02-03 00:00:00.000                                星期六
……
100         2009-12-16 00:00:00.000                                星期天
101         2009-12-22 00:00:00.000                                星期六
102         2009-12-23 00:00:00.000                                星期天
103         2009-12-29 00:00:00.000                                星期六
104         2009-12-30 00:00:00.000                                星期天

*/（所影响的行数为 104 行）




****************************************************************************

declare @t table(dd datetime)
declare @d1 datetime
select @d1='2009-1-1'
while(year(@d1) <2008)
begin
insert into @t select @d1
set @d1=dateadd(dd,1,@d1)
end

select * from @t where datepart(weekday,dd)=6 or datepart(weekday,dd)=7


****************************************************************************
declare @i int
declare @d datetime
set @i=1
set @d='2009-1-1'
Create table #a (Dat datetime)
while year(@d)=2009
begin
insert into #a values (@d )
set @i=@i+1
set @d=dateadd(day,1,@d)
end
select *,datepart(dw,Dat) as a from #a where datepart(dw,Dat) =1 or datepart(dw,Dat) =7

****************************************************************************
/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：XXX
时间：2009-10
*/
DECLARE @Date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WeekDay int
DECLARE @i int

SET DATEFIRST 7 --设置每周的第一天
SET @StartDate='2006-01-01' --统计的开始日期
SET @EndDate='2006-12-31' --统计的结束日期
SET @WeekDay=1 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二
SET @i=DATEPART(weekday,@StartDate)
PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1))
PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1))
IF(@i<=@WeekDay AND @i<7)
SET @i=@WeekDay-@i
ELSE IF(@i<=@WeekDay AND @i=7)
SET @i=@i-@WeekDay
ELSE
SET @i=@@DATEFIRST-@i+@WeekDay

SET @Date=DATEADD(day,@i,@StartDate)
WHILE @Date<=@EndDate
BEGIN
IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)
SET @Date=DATEADD(Week,1,@Date)
END
GO