if object_id('tablename') is not null drop table tablename select 1 as time_id, '2003-07-09' as time_date,003 as [year],0 as week_of_year, '星期日' as [day] into tablename union select 1, '2003-07-10',003,0, '星期一' union select 1, '2003-07-11',003,0, '星期二' ------------------------------------------------ select time_id, time_date, [year], '第' + cast(week_of_year as varchar(2)) + '周（' + cast(month(week_begin) as varchar(2)) + '/' + cast(day(week_begin) as varchar(2)) + '-' + cast(month(week_end) as varchar(2)) + '/' + cast(day(week_end) as varchar(2)) as week_of_year from (select *, dateadd(day, 1 - datepart(weekday, time_date), time_date) as week_begin, dateadd(day, - datepart(weekday, time_date), time_date) as week_end from tablename) a /* time_id time_date year week_of_year 1 003-07-09 003 第20周（7/6-7/12） 1 003-07-10 003 第20周（7/6-7/12） 1 003-07-11 003 第20周（7/6-7/12） */ ------------------------------------------------ drop table tablename
先创建一个用户函数，根据日期得到周开始日期和结束日期 create function getwstend(@resultdate datetime) returns varchar(20) as begin declare @weekstart datetime,@weekend datetime,@dayweek int,@weeknum int set @weeknum = datepart(wk,@resultdate) set @dayweek = datepart(dw,@resultdate) begin set @weekstart = dateadd(dd,1-@dayweek,@resultdate) set @weekend = dateadd(dd,7-@dayweek,@resultdate) end return '第'+convert(varchar,@weeknum)+'周'+'('+right(convert(varchar(10),@weekstart,111),5)+'--'+right(convert(varchar(10),@weekend,111),5)+')' end go 例如： select test.getwstend('2006-4-17') 返回结果是：第16周(04/16--04/22)
