获取最近x个日历周的第一天和第一个月

Question

我想从今天开始最后8周（GETDATE()）因此，所有8周的格式必须为dd/mm。我试过这种

select "start_of_week" = cast(datepart(dd,dateadd(week, datediff(week, 0, getdate()), 0)) as CHAR(2))+'/'+cast(datepart(mm,dateadd(week, datediff(week, 0, getdate()), 0)) as CHAR(2));

这只适用于本周，但如何将其放入curr-1、curr-2的查询和返回中，。。。curr-7周。最终结果必须是一个玩家和每周的表格，格式为dd/mm

Answer 1

也许就这么简单？

WITH EightNumbers(Nmbr) AS
(
          SELECT 0 
    UNION SELECT -1
    UNION SELECT -2
    UNION SELECT -3
    UNION SELECT -4
    UNION SELECT -5
    UNION SELECT -6
    UNION SELECT -7
    UNION SELECT -8
)
SELECT CONVERT(VARCHAR(5),GETDATE()+(Nmbr*7),103)
FROM EightNumbers
ORDER BY Nmbr DESC

如果你需要（正如标题所示）一周的"第一天"，你可以将选择更改为：

SELECT CONVERT(VARCHAR(5),GETDATE()+(Nmbr*7)-DATEPART(dw,GETDATE())+@@DATEFIRST,103)
FROM EightNumbers
ORDER BY Nmbr DESC

请注意，"一周的第一天"取决于您的系统文化。看看@@DATEFIRST！

结果：

28/12
21/12
14/12
07/12
30/11
23/11
16/11
09/11
02/11

Answer 2

开始：

DECLARE @DateTable TABLE ( ADate DATETIME )
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETDATE()
WHILE (SELECT COUNT(*) FROM @DateTable WHERE DATEPART( dw, ADate ) = 2) <= 7
BEGIN
 INSERT INTO @DateTable
 SELECT @CurrentDate
 SET @CurrentDate = DATEADD( dd, -1, @CurrentDate )
END
SELECT "start_of_week" = cast(datepart(dd,dateadd(week, datediff(week, 0, ADate), 0)) as CHAR(2))
                        +'/'+cast(datepart(mm,dateadd(week, datediff(week, 0, ADate), 0)) as CHAR(2)) 
FROM @DateTable 
WHERE DATEPART( dw, ADate ) = 2
DELETE @DateTable

输出

start_of_week
28/12
21/12
14/12
7 /12
30/11
23/11
16/11
9 /11

Answer 3

语法：选择"start_of_week"=cast（datepart（dd，dateadd（周，datediff（周，0，getdate（））-X，0））为CHAR（2））

  select "start_of_week" = 
    cast(datepart(dd,dateadd(week, datediff(week, 0, getdate()) - 0, 0)) as CHAR(2)) ,
"previous_week1" = 
    +'/'+cast(datepart(mm,dateadd(week, datediff(week, 0, getdate()) - 1, 0)) as CHAR(2)),
"previous_week2" = 
    +'/'+cast(datepart(mm,dateadd(week, datediff(week, 0, getdate()) - 2, 0)) as CHAR(2)),
"previous_week3" = 
    +'/'+cast(datepart(mm,dateadd(week, datediff(week, 0, getdate()) - 3, 0)) as CHAR(2));

等等……谢谢

Answer 4

假设sys.all_objects至少有8行，并且您想要一周中的第一天（您在问题中没有指定）：

select top 8 convert(varchar(5),
        dateadd(WEEK,
                1-1* ROW_NUMBER() over(order by newid()),
                 dateadd(DD,
                            1-datepart(dw,getdate()),
                            getdate())),
        1) as [FirstDayOfWeek]
  from sys.all_objects

convert只给出月/日。行号用于给出数字1-8。我把行号乘以-1，再加1得到数字0，-1，-2-7和date将这些（按天）添加到本周的第一天。我通过getdate和date加上一周中第+1天的负版本，找到了本周的第一天。