在SQL Server中获取第一天的第一天 [英] Get first day of week in 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，那么你应该使用 取决于 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.

这篇关于在SQL Server中获取第一天的第一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！