解决这类问题时,利用日历表会更容易。
当你直接使用实际日期而不是日期的一部分时,任务就会变得简单得多。请考虑以下示例(根据OP使用DATEPART函数,假设采用TSQL/SQL Server):
SELECT
CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE) AS RangeStart,
DATEADD(MILLISECOND, -3, CAST(DATEADD(DAY, 7, CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE)) AS DATETIME)) AS RangeEnd;
执行上述代码后,结果如下:
| RangeStart | RangeEnd |
| ---------- | -------------------- |
| 2023-12-03 | 2023-12-09 23:59:59.997 |
接下来,我们可以将这个范围设置为变量,并将其应用于查询:
DECLARE
@RangeStart DATETIME = CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE),
@RangeEnd DATETIME = DATEADD(MILLISECOND, -3, CAST(DATEADD(DAY, 7, CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE)) AS DATETIME));
SELECT
t.id,
DATEPART(WEEK, t.transaction_date) AS week
FROM
table1 t
WHERE
t.transaction_date BETWEEN @RangeStart AND @RangeEnd;
或者不使用变量声明,直接在查询中计算范围:
SELECT
t.id,
DATEPART(WEEK, t.transaction_date) AS week
FROM
table1 t
WHERE
t.transaction_date BETWEEN CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE)
AND DATEADD(MILLISECOND, -3, CAST(DATEADD(DAY, 7, CAST(DATEADD(WEEK, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS DATE)) AS DATETIME));
现在,我们通过减少DECLARE语句,简化了查询。