从SQL中相对于日期的特定周获取数据

目录

介绍

背景

所以我们需要更复杂一点

兴趣点

---

一个QA问题需要“最后一周的数据”，这让我开始思考。使用带有“WW”间隔的DATEADD函数很容易，但这会从今天减去7天。这与“上周”不同，因为“上周”从星期日开始，并在下一个星期日午夜之前结束。或者星期六，如果你在以色列。或者星期一，如果这是您的公司规定。那么你如何解决这个问题呢？

介绍

周是一种痛苦——对我们来说，周是自然的单位，但除了周本身，它们与其他任何东西都不同步：一个月的第一天可能是一周的开始，第二天，第三天，……一直到第七。但是管理层——以及人类——喜欢周：我们的整个生活都是围绕周来安排的，所以我们经常想要“最后几周的销售”或“下几周的交付”。SQL并不能很好地处理这个问题。相信我，这实际上是一个痛苦的处理，因为SQL BETWEEN是包容性的，但是DATETIME值存储在与天数并不完全相关的Ticks中。

背景

显而易见的开始方法是使用带有BETWEEN的WHERE子句：

,,, WHERE InsertDate BETWEEN DATEADD(ww, -1, GETDATE()) AND GETDATE()

但这……有问题。

第一个问题是，这是相对于今天的：今天是星期二，现在是11:40，所以这适用于上星期二的同一时间和今天。不是上周！

而且……它很容易出错。每次使用时GETDATE，它都会从系统获取当前时间，因此连续两次调用可能会返回不同的结果：不同的毫秒、不同的小时，甚至不同的世纪！永远不要多次调用GETDATE：保存它的值并一遍又一遍地使用它。

所以我们需要更复杂一点

首先，让我们创建一些变量以使生活更轻松：

DECLARE @NOW DATETIME, @WS DATETIME, @WE DATETIME
DECLARE @WC INT
SET @WC = 1

@WC 表示要抵消多少周：上周为1，本周为 0，下周为-1，依此类推。

@NOW 获取当前日期和时间，去掉时间部分，并将其保存为“今天午夜”。

SET @NOW = CAST(GETDATE() AS DATE)

@WS和@WE分别是一周的开始和结束。

SET @WS = DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1), @NOW)
SET @WE = DATEADD(WW, 1, @WS)

所以，如果我们要求从我们得到今天( 2021-09-14)开始的上周：

2021-09-05 00:00:00.000  

和：

2021-09-12 00:00:00.000

我们可以直接在WHERE 中使用：

... WHERE InsertDate BETWEEN @WS AND @WE

但这并不是那么简单，因为SQL BETWEEN是包容性的：它返回两个日期之间的所有记录，包括开始日期和结束日期，并且因为SQL将日期存储在与整数秒甚至微秒没有直接关系的Ticks中，我们不能只需从结束日期“减去一个”即可得到“一切都到午夜”。我试过了，减去一毫秒给了我相同的日期时间：

SET @WE =  DATEADD(WW, 1, @WS)
SET @PD =  DATEADD(ms, -1, DATEADD(WW, 1, @WS))
SELECT @NOW, 
       @WS,
       @WE,
       @PD,
       CASE WHEN @PD = @WE THEN 'SAME' ELSE 'DIFFERENT' END

我每次都得到“SAME”...

如果我使用-2，我会得到“DIFFERENT”：

SET @WE =  DATEADD(WW, 1, @WS)
SET @PD =  DATEADD(ms, -2, DATEADD(WW, 1, @WS))
SELECT @NOW, 
       @WS,
       @WE,
       @PD,
       CASE WHEN @PD = @WE THEN 'SAME' ELSE 'DIFFERENT' END

但那是个骗局，我不相信它！

所以，改用这个：

DECLARE @NOW DATETIME, @WS DATETIME, @WE DATETIME
DECLARE @WC INT
SET @WC = 1
SET @NOW = CAST(GETDATE() AS DATE)
SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1), @NOW)
SET @WE =  DATEADD(WW, 1, @WS)

... WHERE InsertDate BETWEEN @WS AND @WE AND InsertDate != @WE

如果您的一周不是从星期日开始，那么只需适当地向前或向后偏移@WS：

-1对于Saturday：

SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1) - 1, @NOW)

+1对于Monday：

SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1) + 1, @NOW)

兴趣点

只有那个SQL是……嗯……在某些方面考虑不周……

https://www.codeproject.com/Tips/5312808/Fetching-Data-from-a-Specific-Week-Relative-to-a-D