SQL Server没有显示给定日期的正确周数

Question

SQL Server在2011年1月1日以外的第一周显示第53周，需要是第1周。

下面是查询和输出:

declare @T table (dt datetime)
insert into @T values
('2010-12-26'),
('2010-12-27'),
('2010-12-28'),
('2010-12-29'),
('2010-12-30'),
('2010-12-31'),
('2011-01-01'),
('2011-01-02'),
('2011-01-03'),
('2011-01-04'),
('2011-01-05'),
('2011-01-06'),
('2011-01-07'),
('2011-01-08')
select dt,DATEPART(wk,dt) from @T

输出:

2010-12-26 00:00:00.000 53
2010-12-27 00:00:00.000 53
2010-12-28 00:00:00.000 53
2010-12-29 00:00:00.000 53
2010-12-30 00:00:00.000 53
2010-12-31 00:00:00.000 53
2011-01-01 00:00:00.000 1
2011-01-02 00:00:00.000 2
2011-01-03 00:00:00.000 2
2011-01-04 00:00:00.000 2
2011-01-05 00:00:00.000 2
2011-01-06 00:00:00.000 2
2011-01-07 00:00:00.000 2
2011-01-08 00:00:00.000 2

我想要SQL Server显示从12月26日到1月1日的第一周。有人知道怎么做吗?

谢谢Aschwin .

Answer 1

这比我最初想象的要困难得多。我在比较去年年底，看看它是否有资格成为新的一年的一部分。如果是，我将周设置为1，否则我只使用正常的周。

declare @T table (dt datetime) 
insert into @T values 
('2010-12-25'), 
('2010-12-26'), 
('2010-12-27'), 
('2010-12-28'), 
('2010-12-29'), 
('2010-12-30'), 
('2010-12-31'), 
('2011-01-01'), 
('2011-01-02'), 
('2011-01-03'), 
('2011-01-04'), 
('2011-01-05'), 
('2011-01-06'), 
('2011-01-07'), 
('2011-01-08'), 
('2011-12-31'),
('2012-01-01')
select dt, 
week = case when dt + 6 - datediff(day, -1, dt) % 7 = dateadd(year, datediff(year,-1, dt), 0)
then 1 else datepart(week, dt) end from @t

证明:

https://data.stackexchange.com/stackoverflow/q/110527/

Answer 2

我不确定它是否适用于所有年份(但看起来像)但您可以使用CASE语句解决此问题。

SELECT  dt
        , CASE  WHEN DATEPART(wk, dt) <> 53 
                THEN DATEPART(wk, dt) 
                ELSE 1 
          END  
FROM    @T

新的ISO_WEEK日期部分不适用于您请求的输出。

Answer 3

我创建了2个函数来处理这个问题一周的第一天或最后一天2)获取周数或年份

函数1

    CREATE FUNCTION [dbo].[fn_GetDayOf]
(
    @Date datetime,
    --@FirstDayOfWeek int = 7,
    @Mode int =1
)
/*
    Mode 1: First Day Of Week
    Mode 2: Last Day Of Week
*/
RETURNS datetime
WITH EXECUTE AS CALLER
BEGIN
    Declare @Return datetime
    --SET DATEFIRST @FirstDayOfWeek
    IF @Mode = 1
    BEGIN
        select @Return = dateadd(day,-(datepart(weekday,@date)-1),convert(date,@date))
    END
    ELSE IF @Mode = 2
    BEGIN
        select @Return = dateadd(SECOND,-1,convert(datetime,dateadd(day,(datepart(weekday,@date)),convert(date,@date))))
    END
    ELSE
    BEGIN
        SET @Return = @Date
    END
    --SET DATEFIRST 7
    RETURN @Return
END

2

函数

    CREATE FUNCTION [dbo].[fn_GetYearWeek]
(
    @Date datetime,
    --@FirstDayOfWeek int = 7,
    @Mode int =1
)
/*
    Mode 1 = Week Number
    Mode 2 = Year
*/
RETURNS INT
BEGIN
    declare @Return int
    IF @Mode = 1
    BEGIN
        select @Return = case when  datepart(week,[dbo].[fn_GetDayOf] (@Date,1)) <> datepart(week,[dbo].[fn_GetDayOf] (@Date,2)) then datepart(week,[dbo].[fn_GetDayOf] (@Date,1)) else datepart(week,[dbo].[fn_GetDayOf] (@Date,2)) end 
    END
    ELSE IF @Mode = 2
    BEGIN
        select @Return = case when  datepart(WEEK,[dbo].[fn_GetDayOf] (@Date,1)) <> datepart(week,[dbo].[fn_GetDayOf] (@Date,2)) then datepart(YEAR,[dbo].[fn_GetDayOf] (@Date,1)) else datepart(YEAR,[dbo].[fn_GetDayOf] (@Date,2)) end 
    END
    ELSE
    BEGIN
        SET @Return = -1
    END
    Return  @Return
END

运行示例

declare @T table (dt datetime) 
insert into @T values 
('2010-12-25'), 
('2010-12-26'), 
('2010-12-27'), 
('2010-12-28'), 
('2010-12-29'), 
('2010-12-30'), 
('2010-12-31'), 
('2011-01-01'), 
('2011-01-02'), 
('2011-01-03'), 
('2011-01-04'), 
('2011-01-05'), 
('2011-01-06'), 
('2011-01-07'), 
('2011-01-08'), 
('2011-12-31'),
('2012-01-01'),
('2012-01-02'),
('2012-12-31'),
('2013-01-01')
select 
    dt,
    datepart(week,dt),
    --case when  datepart(week,[dbo].[fn_GetDayOf] (dt,1)) <> datepart(week,[dbo].[fn_GetDayOf] (dt,2)) then datepart(week,[dbo].[fn_GetDayOf] (dt,1)) else datepart(week,[dbo].[fn_GetDayOf] (dt,2)) end 
    [dbo].[fn_GetYearWeek] (dt,1),
    [dbo].[fn_GetYearWeek] (dt,2)
from @T

结果:

Answer 4

另一种检索当前年份总周数的方法:

DECLARE @LASTDAY DATETIME
DECLARE @weeks INT
SET @LASTDAY = DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))
SELECT @weeks = CASE DATEname(dw,@LASTDAY)
    WHEN 'MONDAY' THEN DATEPART(WK, DATEADD(wk,DATEDIFF(wk,7,@LASTDAY),5))
    WHEN 'TUESDAY' THEN DATEPART(WK, DATEADD(wk,DATEDIFF(wk,7,@LASTDAY),5))
    WHEN 'WEDNESDAY' THEN DATEPART(WK, DATEADD(wk,DATEDIFF(wk,7,@LASTDAY),5))
    ELSE DATEPART(WK, @LASTDAY)
END
select @weeks