1,建序数表 select top 8000 identity(int,1,1) as N into numtab from (select top 100 id=1 from sysobjects) as a, (select top 100 id=1 from sysobjects) as b, (select top 100 id=1 from sysobjects) as c2, select cast(convert(char(4),getdate(),120)+'-01-01' as datetime)+a.n from numtab a where datepart(week,cast(convert(char(4),getdate(),120)+'01-01' as datetime)+a.n)=24 and cast(convert(char(4),getdate(),120)+'-01-01' as datetime)+a.n<=cast(convert(char(4),getdate(),120)+'-12-31' as datetime)
declare @dt datetime declare @dt_f datetime declare @dt_l datetime declare @chr_l char(1) declare @chr_f char(1) set @dt=cast(cast(year(getdate()) as char(4))+ '-01-01' as datetime) set @dt_f='1900-01-01' set @dt_l='1900-01-01' set @chr_f='0' set @chr_l='0' while @chr_l='0' begin if DATEPART ( week ,@dt)=1 and @chr_f='0' begin set @chr_f='1' set @dt_f=@dt end else begin if DATEPART ( week ,@dt)=1 and @chr_f='1' begin set @dt_l=@dt end else begin set @chr_l='1' end
end select @dt set @dt=@dt+1 select @dt end select @dt_f,@dt_l
select mdate,dateadd(dd,-datepart(dw,mdate),mdate),dateadd(dd,6-datepart(dw,mdate),mdate) from test
不好意思有个小错误。现在OK了。 declare @dt datetime declare @dt_f datetime declare @dt_l datetime declare @chr_l char(1) declare @chr_f char(1) set @dt=cast(cast(year(getdate()) as char(4))+ '-01-01' as datetime) set @dt_f='1900-01-01' set @dt_l='1900-01-01' set @chr_f='0' set @chr_l='0' while @chr_l='0' begin if DATEPART ( week ,@dt)=37 and @chr_f='0' begin set @chr_f='1' set @dt_f=@dt end else begin if DATEPART ( week ,@dt)=37 and @chr_f='1' begin set @dt_l=@dt end else begin if @chr_f='1' begin set @chr_l='1' end end end
set @dt=@dt+1
end select @dt_f,@dt_l 星期数我这里用的是37。就是本星期啊。你可以自己更改的。也可以使用参数。
create function fun(@year datetime,@n smallint) RETURNS datetime as begin declare @time datetime select @time=dateadd(week,@n,@year) RETURN @time end定义一个函数,输入你的年代数,和星期数即可select dbo.fun('2003',11) as 开始日期 ,dateadd(week,1,dbo.fun('2003',11)) as 介绍日期
select dateadd(ww,1,getdate())
--当前日期减一星期
select dateadd(ww,-1,getdate())
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c2,
select cast(convert(char(4),getdate(),120)+'-01-01' as datetime)+a.n from numtab a
where datepart(week,cast(convert(char(4),getdate(),120)+'01-01' as datetime)+a.n)=24 and cast(convert(char(4),getdate(),120)+'-01-01' as datetime)+a.n<=cast(convert(char(4),getdate(),120)+'-12-31' as datetime)
declare @dt_f datetime
declare @dt_l datetime
declare @chr_l char(1)
declare @chr_f char(1)
set @dt=cast(cast(year(getdate()) as char(4))+ '-01-01' as datetime)
set @dt_f='1900-01-01'
set @dt_l='1900-01-01'
set @chr_f='0'
set @chr_l='0'
while @chr_l='0'
begin
if DATEPART ( week ,@dt)=1 and @chr_f='0'
begin
set @chr_f='1'
set @dt_f=@dt
end
else
begin
if DATEPART ( week ,@dt)=1 and @chr_f='1'
begin
set @dt_l=@dt
end
else
begin
set @chr_l='1'
end
end
select @dt
set @dt=@dt+1
select @dt
end
select @dt_f,@dt_l
declare @dt datetime
declare @dt_f datetime
declare @dt_l datetime
declare @chr_l char(1)
declare @chr_f char(1)
set @dt=cast(cast(year(getdate()) as char(4))+ '-01-01' as datetime)
set @dt_f='1900-01-01'
set @dt_l='1900-01-01'
set @chr_f='0'
set @chr_l='0'
while @chr_l='0'
begin
if DATEPART ( week ,@dt)=37 and @chr_f='0'
begin
set @chr_f='1'
set @dt_f=@dt
end
else
begin
if DATEPART ( week ,@dt)=37 and @chr_f='1'
begin
set @dt_l=@dt
end
else
begin
if @chr_f='1'
begin
set @chr_l='1'
end
end
end
set @dt=@dt+1
end
select @dt_f,@dt_l
星期数我这里用的是37。就是本星期啊。你可以自己更改的。也可以使用参数。
RETURNS datetime as
begin
declare @time datetime
select @time=dateadd(week,@n,@year)
RETURN @time
end定义一个函数,输入你的年代数,和星期数即可select dbo.fun('2003',11) as 开始日期 ,dateadd(week,1,dbo.fun('2003',11)) as 介绍日期
select @dt_f+1,@dt_l+1
如果还有问题,给我发短信吧。