在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).

我正在尝试按周对记录进行分组，将汇总日期存储为一周的第一天。然而，我使用的用于四舍五入日期的标准技术似乎不能在周中正常工作(尽管它可以在几天、几个月、几年、几个月和任何其他我应用过的时间框架中工作)。

Here is the SQL:

这是SQL:

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

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.

这是一个星期一，而不是一个星期天。选择@@datefirst返回7，这是周日的代码，所以就我所知，服务器是正确设置的。

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.

但我必须做出这样的例外，这让我有点不安。另外，如果这是一个重复的问题，请道歉。我发现了一些相关的问题，但没有一个是专门针对这方面的。

13 个解决方案

#1

117

To answer why you're getting a Monday and not a Sunday:

为了回答你为什么会在周一而不是周日:

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:

你在日期0上增加了几个星期。0日期是什么?1900-01-01。1900-01那天是星期几?星期一。所以在你的代码中，你说，从1900年1月1日星期一开始有多少个星期?我们把这叫做[n]。好，现在把[n]周加到1900年1月1日星期一。你不应该对今天是星期一感到惊讶。DATEADD不知道你想要增加周数，但是直到你到了周日，它只是增加了7天，然后又增加了7天，……就像DATEDIFF只识别被交叉的边界。例如，它们都返回1，尽管有些人抱怨说应该有一些合理的逻辑来进行聚集或聚集:

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

To answer how to get a Sunday:

回答如何获得一个星期天:

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);

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.

如果您更改了DATEFIRST设置(或者您的代码正在为具有不同设置的用户运行)，这将不会中断——前提是您仍然需要一个周日，而不考虑当前设置。如果你想让这两个答案摇摆，那么你应该使用一个依赖于DATEFIRST设置的函数，例如。

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

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

...or...

…或…

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

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:

你有很多选择，但是哪一个表现最好?如果有什么重大的区别，我会很惊讶，但我收集了迄今为止提供的所有答案，并通过两组测试来运行它们——一套便宜，一套昂贵。我测量了客户端统计数据，因为我没有看到I/O或内存在这里的性能中发挥了作用(尽管这些可能会根据函数的使用情况而发挥作用)。在我的测试中，结果是:

"Cheap" assignment query:

“便宜”任务查询:

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

"Expensive" assignment query:

“昂贵”任务查询:

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

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.

如果需要的话，我可以转述我的测试细节——在这里停下来，因为这已经是相当冗长的了。考虑到计算量和内联代码的数量，我有点惊讶地看到Curt在高端市场的表现是最快的。也许我会做一些更彻底的测试并写博客……如果你们不反对我把你们的功能发布到别的地方。

#2

For these that need to get:

对于这些需要得到:

Monday = 1 and Sunday = 7:

周一= 1，周日= 7:

SELECT 1 + ((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Sunday = 1 and Saturday = 7:

星期日= 1，星期六= 7:

SELECT 1 + ((6 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Above there was a similar example, but thanks to double "%7" it would be much slower.

上面有一个类似的例子，但是由于双倍的“%7”，速度会慢得多。

#3

This works wonderfully for me:

这对我来说非常有用:

CREATE FUNCTION [dbo].[StartOfWeek]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

AS
BEGIN
  -- THIS does not work in function.
  -- SET DATEFIRST 1 -- set monday to be the first day of week.

  DECLARE @DOW INT -- to store day of week
  SET @INPUTDATE = CONVERT(VARCHAR(10), @INPUTDATE, 111)
  SET @DOW = DATEPART(DW, @INPUTDATE)

  -- Magic convertion of monday to 1, tuesday to 2, etc.
  -- irrespect what SQL server thinks about start of the week.
  -- But here we have sunday marked as 0, but we fix this later.
  SET @DOW = (@DOW + @@DATEFIRST - 1) %7
  IF @DOW = 0 SET @DOW = 7 -- fix for sunday

  RETURN DATEADD(DD, 1 - @DOW,@INPUTDATE)

END

#4

Googled this script:

用谷歌搜索了这个脚本:

create function dbo.F_START_OF_WEEK
(
    @DATE           datetime,
    -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
    -- Thu = 5, Fri = 6, Sat = 7
    -- Default to Sunday
    @WEEK_START_DAY     int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns     datetime
as
begin
declare  @START_OF_WEEK_DATE    datetime
declare  @FIRST_BOW     datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
    begin
    -- Find first day on or after 1753/1/1 (-53690)
    -- matching day of week of @WEEK_START_DAY
    -- 1753/1/1 is earliest possible SQL Server date.
    select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
    -- Verify beginning of week not before 1753/1/1
    if @DATE >= @FIRST_BOW
        begin
        select @START_OF_WEEK_DATE = 
        dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
        end
    end

return @START_OF_WEEK_DATE

end
go

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

#5

Maybe you need this:

也许你需要这个:

SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

或

DECLARE @MYDATE DATETIME
SET @MYDATE = '2011-08-23'
SELECT DATEADD(DD, 1 - DATEPART(DW, @MYDATE), @MYDATE)

Function

函数

CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
               @pInputDate)

END
GO

#6

CREATE FUNCTION dbo.fnFirstWorkingDayOfTheWeek
(
    @currentDate date
)
RETURNS INT
AS
BEGIN
    -- get DATEFIRST setting
    DECLARE @ds int = @@DATEFIRST 
    -- get week day number under current DATEFIRST setting
    DECLARE @dow int = DATEPART(dw,@currentDate) 

    DECLARE @wd  int =  1+(((@dow+@ds) % 7)+5) % 7  -- this is always return Mon as 1,Tue as 2 ... Sun as 7 

    RETURN DATEADD(dd,1-@wd,@currentDate) 

END

#7

Since Julian date 0 is a Monday just add the number of weeks to Sunday which is the day before -1 Eg. select dateadd(wk,datediff(wk,0,getdate()),-1)

因为朱利安日期0是星期一，所以把星期数加到星期天，也就是前一天。选择返回(周,datediff(周,0,获取当前日期()),1)

#8

For those who need the answer at work and creating function is forbidden by your DBA, the following solution will work:

对于那些在工作中需要答案的人，以及你的DBA禁止创建函数，下面的解决方案是:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart
From.....

This gives the start of that week. Here I assume that Sundays are the start of weeks. If you think that Monday is the start, you should use:

这就给出了那个星期的开始。在这里，我认为星期天是数周的开始。如果你认为星期一是开始，你应该使用:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart
From.....

#9

Set DateFirst 1;

Select 
    Datepart(wk, TimeByDay) [Week]
    ,Dateadd(d,
                CASE 
                WHEN  Datepart(dw, TimeByDay) = 1 then 0
                WHEN  Datepart(dw, TimeByDay) = 2 then -1
                WHEN  Datepart(dw, TimeByDay) = 3 then -2
                WHEN  Datepart(dw, TimeByDay) = 4 then -3
                WHEN  Datepart(dw, TimeByDay) = 5 then -4
                WHEN  Datepart(dw, TimeByDay) = 6 then -5
                WHEN  Datepart(dw, TimeByDay) = 7 then -6
                END
                , TimeByDay) as StartOfWeek

from TimeByDay_Tbl

This is my logic. Set the first of the week to be Monday then calculate what is the day of the week a give day is, then using DateAdd and Case I calculate what the date would have been on the previous Monday of that week.

这是我的逻辑。把一周的第一天设为星期一，然后计算出一个星期的星期几，然后使用DateAdd和Case计算出这个星期前一个星期一的日期。

#10

-1

I don't have any issues with any of the answers given here, however I do think mine is a lot simpler to implement, and understand. I have not run any performance tests on it, but it should be neglegable.

我对这里给出的答案没有任何问题，但是我认为我的实现和理解要简单得多。我还没有对它进行任何性能测试，但它应该是不可接受的。

So I derived my answer from the fact that dates are stored in SQL server as integers, (I am talking about the date component only). If you don't believe me, try this SELECT CONVERT(INT, GETDATE()), and vice versa.

因此，我的答案来自这样一个事实:日期在SQL server中存储为整数(我只讨论日期组件)。如果您不相信我，请尝试这个SELECT CONVERT(INT, GETDATE())，反之亦然。

Now knowing this, you can do some cool math equations. You might be able to come up with a better one, but here is mine.

现在知道了这个，你可以做一些很酷的数学方程。你也许能想出一个更好的，但这是我的。

/*
TAKEN FROM http://msdn.microsoft.com/en-us/library/ms181598.aspx
First day of the week is
1 -- Monday
2 -- Tuesday
3 -- Wednesday
4 -- Thursday
5 -- Friday
6 -- Saturday
7 (default, U.S. English) -- Sunday
*/

--Offset is required to compensate for the fact that my @@DATEFIRST setting is 7, the default. 
DECLARE @offSet int, @testDate datetime
SELECT @offSet = 1, @testDate = GETDATE()

SELECT CONVERT(DATETIME, CONVERT(INT, @testDate) - (DATEPART(WEEKDAY, @testDate) - @offSet))

#11

-1

I had a similar problem. Given a date, I wanted to get the date of the Monday of that week.

我也有类似的问题。给定一个日期，我想知道那个星期的星期一的日期。

I used the following logic: Find the day number in the week in the range of 0-6, then subtract that from the originay date.

我使用了以下逻辑:在0-6的范围内找到一周的天数，然后从原始日期中减去它。

I used: DATEADD(day,-(DATEPART(weekday,)+5)%7,)

我使用:返回(天,-(DATEPART(工作日,)+ 5)% 7)

Since DATEPRRT(weekday,) returns 1 = Sundaye ... 7=Saturday, DATEPART(weekday,)+5)%7 returns 0=Monday ... 6=Sunday.

由于DATEPRRT(工作日)返回1 = Sundaye…周六，DATEPART(工作日，)+5)%7返回0=周一…星期天6 =。

Subtracting this number of days from the original date gives the previous Monday. The same technique could be used for any starting day of the week.

从最初的日期减去这几天就得到了前一个星期一。同样的技术也可以用于每周的任何一天。

#12

-1

I found this simple and usefull. Works even if first day of week is Sunday or Monday.

我发现这个简单实用。即使星期的第一天是星期天或星期一也可以。

DECLARE @BaseDate AS Date

宣布@BaseDate为日期

SET @BaseDate = GETDATE()

设置@BaseDate =获取当前日期()

DECLARE @FisrtDOW AS Date

宣布@FisrtDOW为日期

SELECT @FirstDOW = DATEADD(d,DATEPART(WEEKDAY,@BaseDate) *-1 + 1, @BaseDate)

选择@FirstDOW = DATEADD(d,DATEPART(工作日，@BaseDate) *-1 + 1， @BaseDate)

#13

-3

Maybe I'm over simplifying here, and that may be the case, but this seems to work for me. Haven't ran into any problems with it yet...

也许我把这里化简了，也许是这样，但这对我来说似乎行得通。还没有遇到任何问题……

CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7 - 7) as 'FirstDayOfWeek'
CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7) as 'LastDayOfWeek'

#1

117