Question

sqlserver中如何将第几周转为具体的日期

比如给出2008年第23周，求出具体的年月日

Answer 1

CREATE function Week2Day(@intYear integer,@intWeek integer) returns varchar(50) as begin declare @dateFirstDayOfYear datetime declare @dateFirstDayOfWeek datetime declare @dateLastDayOfWeek datetime set @dateFirstDayOfYear=convert(smalldatetime,convert(varchar(4),@intYear) + '-1-1') set @dateFirstDayOfWeek=dateadd(day,1-datePart(weekday,@dateFirstDayOfYear),dateAdd(week,@intWeek-1,@dateFirstDayOfYear)) set @dateLastDayOfWeek=dateadd(day,6,@dateFirstDayOfWeek) RETURN convert(varchar(10),@dateFirstDayOfWeek,11) + '～' + convert(varchar(10),@dateLastDayOfWeek,11) end

Answer 2

自己把它写成存储过程，传个日期：

DECLARE @NOWDATE VARCHAR(10)
SET @NOWDATE='2015-03-06'

--记录日期第几周
DECLARE @NOW_WW INT
SELECT @NOW_WW=DATEPART(WW,@NOWDATE)
--记录日期星期几
DECLARE @NOW_DW INT
SELECT @NOW_DW=DATEPART(DW,@NOWDATE)

IF(@NOW_DW > 5)
BEGIN
SET @NOW_WW = @NOW_WW + 1
END
ELSE
BEGIN
IF(DAY(@NOWDATE)>=26)
BEGIN
SET @NOW_WW = @NOW_WW + 1
END
END

SELECT @NOW_WW