年周格式怎么改成日期格式---马上送分

[QMSKUMA]

我的年周格式是 200714

我需要的是 20070401 和 20070407

高手指点指点

[dawugui]

declare @str as varchar(6)
set @str = '200714'
declare @year as datetime
set @year = cast(left(@str,4) + '-01-01' as datetime)
declare @week as int
set @week = cast(substring(@str,5,len(@str)) as int)

SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(week,@week,@year)), 0)-1 as 周日, DATEADD(wk, DATEDIFF(wk,0,dateadd(week,@week,@year)), 0)+5 as 周六

周日 周六
------------------------------------------------------ ------------------------------------------------------
2007-04-08 00:00:00.000 2007-04-14 00:00:00.000

（所影响的行数为 1 行）

[zlp321002]

declare @time int,@year int,@month int
set @time=200714
set @year=left(@time,4)
set @month=right(@time,2)
select 本周一=dateadd(wk,@month-1,cast(ltrim(@year)+'-1-1' as datetime)),
本周日=dateadd(day,6,dateadd(wk,@month-1,cast(ltrim(@year)+'-1-1' as datetime)))
/*
本周一 本周日
----------------------- -----------------------
2007-04-02 00:00:00.000 2007-04-08 00:00:00.000

(1 行受影响)

*/

[believe209]

学习!

[hrb2008]

create table #
(col datetime)
declare @s varchar(10),@i int
set @s='200714'
set @i=0
while @i<7
begin
insert into # select dateadd(day,@i,dateadd(wk,cast(right(@s,2) as int)-1,convert(datetime,left(@s,4)+'-1-1')))
set @i=@i+1
end
select * from #
drop table #
/*
col
-----------------------
2007-04-02 00:00:00.000
2007-04-03 00:00:00.000
2007-04-04 00:00:00.000
2007-04-05 00:00:00.000
2007-04-06 00:00:00.000
2007-04-07 00:00:00.000
2007-04-08 00:00:00.000
*/