SQL Server 2005 中 DATEDIFF 如何计算周差异？

发布于 2024-08-30 12:31:01 字数 780 浏览 1 评论 0原文

我想计算两个日期之间的周之差，其中如果两个日期的前一个星期日相同，则它们被视为同一周的一部分。理想情况下，我想使用 DATEDIFF 来执行此操作，而不是学习复杂的习惯用法来计算值。但我不知道当涉及几周时它是如何运作的。

以下查询返回 1 和 2。如果您的日历周以星期日开始，即如果您事先运行 SET DATEFIRST 7 或如果 @@DATEFIRST 为 7，则这可能有意义默认情况下。

SET DATEFIRST 7;
-- SET DATEFIRST 1;

DECLARE
    @d1 DATETIME,
    @d2a DATETIME,
    @d2b DATETIME
;
SELECT
    @d1 = '2010-04-05',   -- Monday
    @d2a = '2010-04-16',  -- Following Friday
    @d2b = '2010-04-18'   -- the Sunday following
;

SELECT
    DATEDIFF(week, @d1, @d2a) AS weekdiff_a   -- returns 1
    ,DATEDIFF(week, @d1, @d2b) AS weekdiff_b  -- returns 2
;

因此，如果执行 SET DATEFIRST 1 而不是 SET DATEFIRST 7，我预计会有不同的结果。但无论如何，返回值是相同的！

这是怎么回事？我应该怎样做才能获得正确的周差？

原文

I would like to calculate the difference in weeks between two dates, where two dates are considered part of the same week if their preceding Sunday is the same. Ideally, I'd like to do this using DATEDIFF, instead of learning an elaborate idiom to calculate the value. But I can't tell how it works when weeks are involved.

The following query returns 1 and 2. This might make sense if your calendar week begins with a Sunday, i.e. if you run SET DATEFIRST 7 beforehand or if @@DATEFIRST is 7 by default.

SET DATEFIRST 7;
-- SET DATEFIRST 1;

DECLARE
    @d1 DATETIME,
    @d2a DATETIME,
    @d2b DATETIME
;
SELECT
    @d1 = '2010-04-05',   -- Monday
    @d2a = '2010-04-16',  -- Following Friday
    @d2b = '2010-04-18'   -- the Sunday following
;

SELECT
    DATEDIFF(week, @d1, @d2a) AS weekdiff_a   -- returns 1
    ,DATEDIFF(week, @d1, @d2b) AS weekdiff_b  -- returns 2
;

So I expected different results if SET DATEFIRST 1 is executed instead of SET DATEFIRST 7. But the return values are the same, regardless!

What is going on here? What should I do to get the correct week differences?

分享到QQ

分享到微博