当前位置： 文江博客话题详情

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

发布于 2024-12-01 08:19:57 字数 553 浏览 1 评论 0原文

我试图按周对记录进行分组，将聚合日期存储为一周的第一天。然而，我用于四舍五入日期的标准技术似乎无法在几周内正常工作（尽管它可以在天、月、年、季度和我应用的任何其他时间范围内正常工作）。

以下是 SQL：

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

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

我可以通过将上面的代码更改为：

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

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

原文

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:

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.

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

无名指的心愿 2024-12-08 08:19:57

要回答为什么是星期一而不是星期日：

您要在日期 0 的基础上添加周数。日期 0 是什么？ 1900 年 1 月 1 日。 1900 年 1 月 1 日是什么日子？周一。所以在你的代码中你会说，自 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');

要回答如何获得星期日：

如果您想要星期日，则选择一个不是星期一而是星期日的基准日期。例如：

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

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

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

，如果您更改 DATEFIRST > 设置为周一、周二，你有什么，行为就会改变。根据您想要的行为，您可以使用以下功能之一：

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 或内存在性能中发挥作用（尽管这些可能会发挥作用，具体取决于函数的使用方式）。在我的测试中，结果是：

“便宜”的分配查询：

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 成为高端最快的。也许我会进行一些更彻底的测试并在博客中介绍它......如果你们不反对我在其他地方发布您的函数。

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:

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.

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:

"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.

回复 原文
胡渣熟男 2024-12-08 08:19:57

对于那些需要获取的：

星期一 = 1 和星期日 = 7：

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

星期日 = 1 和星期六 = 7：

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

上面有一个类似的例子，但由于双“％7”，它会慢得多。

For these that need to get:

Monday = 1 and Sunday = 7:

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

Sunday = 1 and Saturday = 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.

回复 原文
不忘初心 2024-12-08 08:19:57

对于那些在工作中需要答案并且 DBA 禁止创建函数的人，以下解决方案将起作用：

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

这给出了那一周的开始。在这里，我假设星期日是几周的开始。如果您认为星期一是开始，您应该使用：

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

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

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.....
回复 原文
不知所踪 2024-12-08 08:19:57

也许您需要这个：

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

或

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

功能

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

Maybe you need this:

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

Or

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
回复 原文
习惯成性 2024-12-08 08:19:57

这对我来说非常有效：

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

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
回复 原文
命硬 2024-12-08 08:19:57

谷歌搜索这个脚本：

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

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

回复 原文
懷念過去 2024-12-08 08:19:57
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

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
回复 原文
灵芸 2024-12-08 08:19:57

对于基本的（本周的星期日）

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) as date)

如果上周：

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) -7 as date)

在内部，我们构建了一个可以执行此操作的函数，但如果您需要快速而肮脏的功能，这将可以实现。

For the basic (the current week's Sunday)

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) as date)

If previous week:

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) -7 as date)

Internally, we built a function that does it but if you need quick and dirty, this will do it.

回复 原文
臻嫒无言 2024-12-08 08:19:57

由于儒略日期 0 是星期一，只需将星期数添加到星期日
这是-1 的前一天，例如。选择 dateadd(wk,datediff(wk,0,getdate()),-1)

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)

回复 原文
似狗非友 2024-12-08 08:19:57

我发现其他一些答案很冗长，或者如果您希望星期一作为一周的开始，则实际上并不起作用。

周日

SELECT DATEADD(week, DATEDIFF(week, -1, GETDATE()), -1) AS Sunday;

周一

SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE() - 1), 0) AS Monday;

I found some of the other answers long-winded or didn't actually work if you wanted Monday as the start of the week.

Sunday

SELECT DATEADD(week, DATEDIFF(week, -1, GETDATE()), -1) AS Sunday;

Monday

SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE() - 1), 0) AS Monday;
回复 原文
半世晨晓 2024-12-08 08:19:57
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

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

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.

回复 原文
瑾兮 2024-12-08 08:19:57

这对我来说是一个有用的功能

/* MeRrais 211126 
select [dbo].[SinceWeeks](0,NULL)
select [dbo].[SinceWeeks](5,'2021-08-31')
*/
alter Function [dbo].[SinceWeeks](@Weeks int, @From datetime=NULL)
Returns date
AS
Begin   

    if @From is null 
        set @From=getdate()

    return cast(dateadd(day, -(@Weeks*7+datepart(dw,@From)-1), @From) as date)
END

This is a useful function for me

/* MeRrais 211126 
select [dbo].[SinceWeeks](0,NULL)
select [dbo].[SinceWeeks](5,'2021-08-31')
*/
alter Function [dbo].[SinceWeeks](@Weeks int, @From datetime=NULL)
Returns date
AS
Begin   

    if @From is null 
        set @From=getdate()

    return cast(dateadd(day, -(@Weeks*7+datepart(dw,@From)-1), @From) as date)
END
回复 原文
萌辣 2024-12-08 08:19:57

我对这里给出的任何答案都没有任何问题，但我确实认为我的答案更容易实现和理解。我还没有对其进行任何性能测试，但它应该可以忽略不计。

所以我从日期作为整数存储在 SQL Server 中这一事实得出了我的答案（我只讨论日期组件）。如果您不相信我，请尝试此 SELECT CONVERT(INT, GETDATE())，反之亦然。

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

/*
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))

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.

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))
回复 原文
于我来说 2024-12-08 08:19:57

我有类似的问题。给定一个日期，我想获取该周星期一的日期。

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

我使用： DATEADD(day,-(DATEPART(weekday,)+5)%7,)

由于 DATEPRRT(weekday,) 返回 1 = Sundaye ... 7=星期六，
DATEPART(weekday,)+5)%7 返回 0=星期一 ... 6=星期日。

从原始日期减去此天数得出上周一。相同的技术可用于一周中的任何开始日。

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.

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

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

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.

回复 原文
莳間冲淡了誓言ζ 2024-12-08 08:19:57

我发现这简单又有用。即使一周的第一天是星期日或星期一也有效。

声明 @BaseDate AS 日期

SET @BaseDate = GETDATE()

声明 @FisrtDOW AS 日期

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

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

DECLARE @BaseDate AS Date

SET @BaseDate = GETDATE()

DECLARE @FisrtDOW AS Date

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

回复 原文
黯淡〆 2024-12-08 08:19:57

也许我在这里过于简化了，情况可能就是这样，但这似乎对我有用。目前还没有遇到任何问题...

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'

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'
回复 原文
~没有更多了~

关于作者

昵称有卵用

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

吝吻

文章 0 评论 0

Jasmine

文章 0 评论 0

∞梦里开花

文章 0 评论 0

阳光①夏

文章 0 评论 0

暮念

文章 0 评论 0

梦里泪两行

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文