select top 100 identity(int ,1,1) as id into #aa from sysobjects a , sysobjects b --100为2个日期相差的天数select dateadd(dd,id-1,'2007-1-1') from #aa where DATEPART(weekday,dateadd(dd,id-1,'2007-1-1'))=6 and id<datediff(dd,'2007-1-1','2007-2-1')
if object_id('tempdb..#tmp') is not null drop table #tmp GO ----生成循环用的临时表 select top 100 id=identity(int,0,1) into #tmp from syscolumns,sysobjects ----查询 declare @dt1 datetime,@dt2 datetime set @dt1 = '2007-1-1' set @dt2 = '2007-2-1' set datefirst 1 /*设置星期一为每周的起始*/ select dateadd(dd,id,@dt1) from #tmp where dateadd(dd,id,@dt1) <= @dt2 and datepart(weekday,dateadd(dd,id,@dt1)) = 6 ----清除测试环境 drop table #tmp/*结果 2007-01-06 00:00:00.000 2007-01-13 00:00:00.000 2007-01-20 00:00:00.000 2007-01-27 00:00:00.000 */
declare @dtBegin datetime declare @dtEnd datetime declare @t table(dt datetime,name varchar(20))set datefirst 1 set @dtBegin='2007-1-1' set @dtEnd='2007-2-1'while @dtBegin<@dtEnd begin if datepart(weekday,@dtBegin)=6 begin insert @t select @dtBegin,'星期六' end set @dtBegin=@dtBegin+1 end select * from @tdt name ------------------------------------------------------ -------------------- 2007-01-06 00:00:00.000 星期六 2007-01-13 00:00:00.000 星期六 2007-01-20 00:00:00.000 星期六 2007-01-27 00:00:00.000 星期六(所影响的行数为 4 行)
declare @StartDate datetime,@EndDate datetime select @StartDate ='2007/06/01' ,@EndDate ='2007/06/30'declare @DayCount int set @DayCount =datediff(day,@StartDate,@EndDate)+1select ID=Identity(int,1,1) into # from sysobjects as a ,syscolumns as b select ID,dateadd(day,ID-1,@StartDate) as Saturday from # where ID<=@DayCount and datepart(weekday,dateadd(day,ID-1,@StartDate)) =7drop table #go
--drop table #AA declare @ad datetime create table #AA (ad datetime) set @ad='2007-1-1' while @ad<='2007-2-1' begin insert into #AA values (@ad) set @ad=dateadd(d,1,@ad) endselect * from #AA where datepart(dw,ad)=7ad ------------------------------------------------------ 2007-01-06 00:00:00.000 2007-01-13 00:00:00.000 2007-01-20 00:00:00.000 2007-01-27 00:00:00.000(4 row(s) affected)
declare @dt datetimeset @dt = '2007-1-1'while @dt < '2007-2-4' begin set @dt =dateadd(dd,1,@dt) if ( datepart(weekday, @dt) =7) print Convert(varchar(10),@dt , 120) end 2007-01-06 2007-01-13 2007-01-20 2007-01-27 2007-02-03
create table #AA (ad datetime)declare @dt datetimeset @dt = '2007-1-1'while @dt < '2007-2-4' begin set @dt =dateadd(dd,1,@dt) if ( datepart(weekday, @dt) =7) insert into #AA select Convert(varchar(10),@dt , 120) endselect * from #AA drop table #AA2007-01-06 00:00:00.000 2007-01-13 00:00:00.000 2007-01-20 00:00:00.000 2007-01-27 00:00:00.000 2007-02-03 00:00:00.000
--100为2个日期相差的天数select dateadd(dd,id-1,'2007-1-1') from #aa where DATEPART(weekday,dateadd(dd,id-1,'2007-1-1'))=6 and id<datediff(dd,'2007-1-1','2007-2-1')
drop table #tmp
GO
----生成循环用的临时表
select top 100 id=identity(int,0,1) into #tmp from syscolumns,sysobjects
----查询
declare @dt1 datetime,@dt2 datetime
set @dt1 = '2007-1-1'
set @dt2 = '2007-2-1'
set datefirst 1 /*设置星期一为每周的起始*/
select dateadd(dd,id,@dt1) from #tmp
where dateadd(dd,id,@dt1) <= @dt2 and datepart(weekday,dateadd(dd,id,@dt1)) = 6
----清除测试环境
drop table #tmp/*结果
2007-01-06 00:00:00.000
2007-01-13 00:00:00.000
2007-01-20 00:00:00.000
2007-01-27 00:00:00.000
*/
declare @dtEnd datetime
declare @t table(dt datetime,name varchar(20))set datefirst 1
set @dtBegin='2007-1-1'
set @dtEnd='2007-2-1'while @dtBegin<@dtEnd
begin
if datepart(weekday,@dtBegin)=6
begin
insert @t select @dtBegin,'星期六'
end
set @dtBegin=@dtBegin+1
end
select * from @tdt name
------------------------------------------------------ --------------------
2007-01-06 00:00:00.000 星期六
2007-01-13 00:00:00.000 星期六
2007-01-20 00:00:00.000 星期六
2007-01-27 00:00:00.000 星期六(所影响的行数为 4 行)
select @StartDate ='2007/06/01'
,@EndDate ='2007/06/30'declare @DayCount int
set @DayCount =datediff(day,@StartDate,@EndDate)+1select ID=Identity(int,1,1) into # from sysobjects as a ,syscolumns as b select ID,dateadd(day,ID-1,@StartDate) as Saturday from # where ID<=@DayCount and datepart(weekday,dateadd(day,ID-1,@StartDate)) =7drop table #go
declare @ad datetime
create table #AA (ad datetime)
set @ad='2007-1-1'
while @ad<='2007-2-1'
begin
insert into #AA values (@ad)
set @ad=dateadd(d,1,@ad)
endselect * from #AA where datepart(dw,ad)=7ad
------------------------------------------------------
2007-01-06 00:00:00.000
2007-01-13 00:00:00.000
2007-01-20 00:00:00.000
2007-01-27 00:00:00.000(4 row(s) affected)
begin
set @dt =dateadd(dd,1,@dt)
if ( datepart(weekday, @dt) =7)
print Convert(varchar(10),@dt , 120)
end
2007-01-06
2007-01-13
2007-01-20
2007-01-27
2007-02-03
begin
set @dt =dateadd(dd,1,@dt)
if ( datepart(weekday, @dt) =7)
insert into #AA select Convert(varchar(10),@dt , 120)
endselect * from #AA
drop table #AA2007-01-06 00:00:00.000
2007-01-13 00:00:00.000
2007-01-20 00:00:00.000
2007-01-27 00:00:00.000
2007-02-03 00:00:00.000