使用 DATEPART 按星期一计算周数
我试图使用这个简单的查询来跟踪每周的订单，一周从星期一开始。问题是，虽然计数正确，但所有周数都是递增的（第 1 周显示为 2 等）
SET DATEFIRST 1;
SELECT DATEPART(yyyy,[ORDER-DATE]) as Year, DATEPART(wk, [ORDER-DATE]) as Week, count(*) as Count from PAYMENTS
where [ORDER-DATE] >= '2022-01-03' and [ORDER-DATE] <= '2022-01-31'
group by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE])
order by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE]);
输出如下所示：
Year Week Count
2022 2 25
2022 3 15
2022 4 19
2022 5 31
现在，第一周计数是正确的（2022 年 1 月 3 日至 1 月 9 日之间有 25 笔付款）。然而，它显示为第 2 周。奇怪的是，如果我在 2019 年运行相同的查询（开始日期为 2019 年 12 月 30 日），它实际上会按预期工作，并且第 1 周会显示且准确！
如果我将第一个查询日期更改为从“2022-01-01”而不是“2022-01-03”开始，则会将第 1 周显示为仅 1 月 1 日和 1 月 2 日（周末）。
编辑：
期望的结果如下：
Year Week Count
2022 1 25
2022 2 15
2022 3 19
2022 4 31
I am trying to track orders per week using this simple query, where the week starts on Monday. The issue is, while the count is correct, all the weeks are one up (week 1 is displayed as 2 etc)
SET DATEFIRST 1;
SELECT DATEPART(yyyy,[ORDER-DATE]) as Year, DATEPART(wk, [ORDER-DATE]) as Week, count(*) as Count from PAYMENTS
where [ORDER-DATE] >= '2022-01-03' and [ORDER-DATE] <= '2022-01-31'
group by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE])
order by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE]);
The output looks like this:
Year Week Count
2022 2 25
2022 3 15
2022 4 19
2022 5 31
Now, the first week count is correct (25 payments between January 3rd and January 9th, 2022). However it's showing that as week 2. Weirdly enough, if I run the same query for 2019 (start date being 2019-12-30) it actually works as expected and week 1 is displayed and accurate!
If I change the first queries date to start on "2022-01-01" instead of "2022-01-03", it shows week 1 as only being January 1st and January 2nd (weekend).
EDIT:
The desired result is like follows:
Year Week Count
2022 1 25
2022 2 15
2022 3 19
2022 4 31
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
2022 年从星期六开始。 SQL Server DATEPART() 始终将 1 月 1 日视为第 1 周。使用
SET DATEFIRST 1，2022 年 1 月 3 日星期一将成为第 2 周的开始。如果您不关心周末，也许您应该将您的周重新定义为周六至周五。这可以通过
SET DATEFIRST 6来完成。请参阅此数据库<>fiddle。
Year 2022 started on a Saturday. SQL Server DATEPART() always treats January 1 as week 1. With
SET DATEFIRST 1, Monday 2022-01-03 becomes the start of week 2.If you don't care about weekends, perhaps you should redefine your weeks as Saturday through Friday. This can be done with
SET DATEFIRST 6.See this db<>fiddle.