select Fday from freeday where datepart(weekday,Fday) = 1
--生成测试数据表 create table freeday(Fday datetime) go--创建存储过程 create procedure sp_getSunday(@year int) as begin declare @date char(10) set @date = rtrim(@year)+'-01-01' while datepart(dw,@date) != 7 set @date = dateadd(dd,1,@date) set rowcount 54 select identity(int,0,1) as id into #T from sysobjects set rowcount 0 insert into freeday select dateadd(dd,7*id,@date) from #T where year(dateadd(dd,7*id,@date)) = @year
end go--执行存储过程调用 exec sp_getSunday 2004--查看执行结果 select * from freeday--删除测试环境 drop procedure sp_getSunday drop table freeday go
上面给出的程序代码有bug,修改一下: ----------------------------------------------------------------- --生成测试数据表 create table freeday(Fday datetime) go--创建存储过程 create procedure sp_getSunday(@year int) as begin --将Monday设置为每周的开始 set datefirst 1 declare @date char(10) set @date = rtrim(@year)+'-01-01' while datepart(dw,@date) != 7 set @date = dateadd(dd,1,@date) set rowcount 54 select identity(int,0,1) as id into #T from sysobjects set rowcount 0 insert into freeday select dateadd(dd,7*id,@date) from #T where year(dateadd(dd,7*id,@date)) = @year
end go--执行存储过程调用 exec sp_getSunday 2004--查看执行结果 select * from freeday--删除测试环境 drop procedure sp_getSunday drop table freeday go
create table freeday(Fday datetime)
go--创建存储过程
create procedure sp_getSunday(@year int)
as
begin
declare @date char(10)
set @date = rtrim(@year)+'-01-01'
while datepart(dw,@date) != 7
set @date = dateadd(dd,1,@date) set rowcount 54
select identity(int,0,1) as id into #T from sysobjects
set rowcount 0 insert into freeday
select dateadd(dd,7*id,@date) from #T where year(dateadd(dd,7*id,@date)) = @year
end
go--执行存储过程调用
exec sp_getSunday 2004--查看执行结果
select * from freeday--删除测试环境
drop procedure sp_getSunday
drop table freeday
go
我的SQL中datepart(weekday,'2005-10-9')=1,你最好也试一下
-----------------------------------------------------------------
--生成测试数据表
create table freeday(Fday datetime)
go--创建存储过程
create procedure sp_getSunday(@year int)
as
begin
--将Monday设置为每周的开始
set datefirst 1 declare @date char(10)
set @date = rtrim(@year)+'-01-01'
while datepart(dw,@date) != 7
set @date = dateadd(dd,1,@date) set rowcount 54
select identity(int,0,1) as id into #T from sysobjects
set rowcount 0 insert into freeday
select dateadd(dd,7*id,@date) from #T
where year(dateadd(dd,7*id,@date)) = @year
end
go--执行存储过程调用
exec sp_getSunday 2004--查看执行结果
select * from freeday--删除测试环境
drop procedure sp_getSunday
drop table freeday
go
非常感谢!!!!