如何使用 SQL Server 查找周一至周五的上周日期？

Question

我想查找上周的日期

SELECT *
FROM Table
Where [Date] Between '04-Jan-2016' AND '15-Jan-2016'

每次我运行 SQL 查询时，它都需要显示 last week 。

原文由 PriceCheaperton 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Sunday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 2-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Monday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 3-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Tuesday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 4-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Wednesday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Thursday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 6-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Friday'
union all
SELECT DATEADD(wk, -1, DATEADD(DAY, 7-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Saturday'

原文由 Grodz 发布，翻译遵循 CC BY-SA 4.0 许可协议