在 SQL Server 中获取一周的第一天

2021-12-02 00:00:00 date tsql sql-server-2008 sql-server

我正在尝试按周对记录进行分组，将聚合日期存储为一周的第一天.但是，我用于四舍五入日期的标准技术似乎无法在周内正常工作(尽管它适用于天、月、年、季度和我应用的任何其他时间范围).

I am trying to group records by week, storing the aggregated date as the first day of the week. However, the standard technique I use for rounding off dates does not appear to work correctly with weeks (though it does for days, months, years, quarters and any other timeframe I've applied it to).

这是SQL:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), 0);

这将返回 2011-08-22 00:00:00.000，这是星期一，而不是星期日.选择 @@datefirst 返回 7，这是星期日的代码，所以据我所知服务器设置正确.

This returns 2011-08-22 00:00:00.000, which is a Monday, not a Sunday. Selecting @@datefirst returns 7, which is the code for Sunday, so the server is setup correctly in as far as I know.

通过将上面的代码更改为:

I can bypass this easily enough by changing the above code to:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1);

但我不得不做出这样一个例外的事实让我有点不安.另外，如果这是一个重复的问题，我们深表歉意.我发现了一些相关的问题，但没有一个专门针对这方面.

But the fact that I have to make such an exception makes me a little uneasy. Also, apologies if this is a duplicate question. I found some related questions but none that addressed this aspect specifically.

推荐答案

要回答为什么你得到的是星期一而不是星期日:

您要在日期 0 上加上周数.什么是日期 0?1900-01-01.1900-01-01 是哪一天?周一.所以在您的代码中，您是说，自 1900 年 1 月 1 日星期一以来已经过去了多少周?我们称之为[n].好的，现在将 [n] 周添加到 1900 年 1 月 1 日星期一.您应该不会对这最终成为星期一感到惊讶.DATEADD 不知道你想增加周数，但直到你到星期天，它只是增加 7 天，然后再增加 7 天，......就像 DATEDIFF 只识别已经跨越的边界.例如，这些都返回 1，尽管有些人抱怨应该内置一些合理的逻辑来向上或向下取整:

You're adding a number of weeks to the date 0. What is date 0? 1900-01-01. What was the day on 1900-01-01? Monday. So in your code you're saying, how many weeks have passed since Monday, January 1, 1900? Let's call that [n]. Ok, now add [n] weeks to Monday, January 1, 1900. You should not be surprised that this ends up being a Monday. DATEADD has no idea that you want to add weeks but only until you get to a Sunday, it's just adding 7 days, then adding 7 more days, ... just like DATEDIFF only recognizes boundaries that have been crossed. For example, these both return 1, even though some folks complain that there should be some sensible logic built in to round up or down:

SELECT DATEDIFF(YEAR, '2010-01-01', '2011-12-31');
SELECT DATEDIFF(YEAR, '2010-12-31', '2011-01-01');

要回答如何获得星期日:

如果您想要星期日，请选择一个不是星期一而是星期日的基准日期.例如:

If you want a Sunday, then pick a base date that's not a Monday but rather a Sunday. For example:

DECLARE @dt DATE = '1905-01-01';
SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

如果您更改 DATEFIRST 设置(或者您的代码正在为具有不同设置的用户运行)，这不会中断 - 前提是无论当前设置如何，您仍然想要一个星期天.如果你想要 jive 的这两个答案，那么你应该使用一个 does 依赖于 DATEFIRST 设置的函数，例如

This will not break if you change your DATEFIRST setting (or your code is running for a user with a different setting) - provided that you still want a Sunday regardless of the current setting. If you want those two answers to jive, then you should use a function that does depend on the DATEFIRST setting, e.g.

SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP);

因此，如果您将 DATEFIRST 设置更改为星期一、星期二，那么行为将会改变.根据您想要的行为，您可以使用以下功能之一:

So if you change your DATEFIRST setting to Monday, Tuesday, what have you, the behavior will change. Depending on which behavior you want, you could use one of these functions:

CREATE FUNCTION dbo.StartOfWeek1 -- always a Sunday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @d), '19050101'));
END
GO

...或...

CREATE FUNCTION dbo.StartOfWeek2 -- always the DATEFIRST weekday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, @d), @d));
END
GO

现在，您有很多选择，但哪一个效果最好?如果会有任何重大差异，我会感到惊讶，但我收集了迄今为止提供的所有答案，并通过两组测试对它们进行了测试 - 一组便宜，一组昂贵.我测量了客户端统计数据，因为我没有看到 I/O 或内存在此处的性能中发挥作用(尽管这些可能会发挥作用，具体取决于函数的使用方式).在我的测试中，结果是:

Now, you have plenty of alternatives, but which one performs best? I'd be surprised if there would be any major differences but I collected all the answers provided so far and ran them through two sets of tests - one cheap and one expensive. I measured client statistics because I don't see I/O or memory playing a part in the performance here (though those may come into play depending on how the function is used). In my tests the results are:

廉价"分配查询:

Function - client processing time / wait time on server replies / total exec time
Gandarez     - 330/2029/2359 - 0:23.6
me datefirst - 329/2123/2452 - 0:24.5
me Sunday    - 357/2158/2515 - 0:25.2
trailmax     - 364/2160/2524 - 0:25.2
Curt         - 424/2202/2626 - 0:26.3

昂贵"的分配查询:

Function - client processing time / wait time on server replies / total exec time
Curt         - 1003/134158/135054 - 2:15
Gandarez     -  957/142919/143876 - 2:24
me Sunday    -  932/166817/165885 - 2:47
me datefirst -  939/171698/172637 - 2:53
trailmax     -  958/173174/174132 - 2:54

如果需要，我可以传达我的测试细节 - 在这里停止，因为这已经变得很冗长了.考虑到计算和内联代码的数量，我有点惊讶地看到 Curt 以最快的速度出现在高端.也许我会运行一些更彻底的测试并在博客上讨论它...如果你们不反对我在其他地方发布你的函数.

I can relay the details of my tests if desired - stopping here as this is already getting quite long-winded. I was a bit surprised to see Curt's come out as the fastest at the high end, given the number of calculations and inline code. Maybe I'll run some more thorough tests and blog about it... if you guys don't have any objections to me publishing your functions elsewhere.

相关文章