如何在SQL中基于当前日期获取上周日期范围？

Question

我在Crystal Reports中有这段代码，它基于当前日期给出上周的日期范围。

本周第一天：

If DayOfWeek(currentdate) = 2 Then
currentdate
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",-1,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",-2,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",-3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",-4,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",-5,currentdate)
Else If DayOfWeek(currentdate) = 1 Then
dateadd ("d",-6,currentdate)

本周的最后一天：

If DayOfWeek(currentdate) = 2 Then
dateadd ("d",+6,currentdate)
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",+5,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",+4,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",+3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",+2,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",+1,currentdate)
Else If DayOfWeek(currentdate) = 1 then currentdate

如何使用两个变量存储周一（startdate）和周日（enddate），在SQL中进行相同的操作？

我在这个网站上找到了select datepart(dw,getdate()) --6，但我不知道如何使用它。

- asfsdf

Answer 1

我在parms CTE中生成了一些间隔日期，然后从parms中选择CurrentDate，上周日和上周六。我假设你想要日期范围为周日至周六。

周日-周六日期范围

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekSunday   = DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
FROM parms

周一到周日范围

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekMonday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
     , LastWeekSunday   = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
FROM parms

如果您只想从今天开始，而不是从日期列中选择上周的星期一到上周的星期日，您可以使用以下方法：

SELECT CURRENT_TIMESTAMP
     , LastWeekSunday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))

- Bill Stidham

---

我需要星期一到星期日。 - asfsdf

@cojimarmiami，星期一到星期日的数字有些不同。我添加了用于星期一至星期日的代码，但保留了星期日至星期六的代码，并对两者进行了标记。 - Bill Stidham

请问您能否解释一下这个语句背后的逻辑，因为当我运行它时，我得到了6个不同的LastWeekMonday和LastWeekSunday。 - asfsdf

parms CTE 只是用来生成一些日期（实际上是六行）。这就是为什么你会得到六行结果的原因。你只需要在最后使用 SELECT 语句，并将 "parms" 替换为你实际的表名即可。 - Bill Stidham

@cojimarmiami，我的代码还假设您正在从名为CurrentDate的表中的日期列中进行选择。我添加了第三个部分，使用今天的日期获取上一周的日期范围。 - Bill Stidham

Answer 2

这个解决方案经过测试可以使用。我得到了上一周的星期一和星期日作为上下限。

SELECT
    -- 17530101 or 1753-01-01 is the minimum date in SQL Server
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AS [LowerLimit], -- Last Week's Monday
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101') AS [UpperLimit] -- Last Week's Sunday.

这可以在真实世界的查询中像这样使用：

SELECT
    *
FROM
    SomeTable
WHERE
    SomeTable.[Date] >= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AND
    SomeTable.[Date] <= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101')

以下是一些测试：

1. 闰年

当前日期：2016-02-29 00:00:00.000

结果：

LowerLimit                 UpperLimit
2016-02-22 00:00:00.000    2016-02-28 00:00:00.000

2. 上周属于不同的年份

当前日期：2016-01-06 00:00:00.000

LowerLimit                 UpperLimit
2015-12-28 00:00:00.000    2016-01-03 00:00:00.000

3. 上月下限和本月上限

当前日期：2016-05-04 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

4. 当前日期是星期日

当前日期：2016-05-08 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

- Rafael

Answer 3

Bill的代码很好，但是如果我想根据今天的日期获得上周日到上周六的数据，我需要进行一些小的编辑。

select CURRENT_TIMESTAMP, 
LastWeekSunday   = DATEADD(dd,  -1, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0)), 
LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0))

Rgds - Matt

- blugecko

Answer 4

对于与日期和日期间隔有关的所有内容，我几乎总是建议使用日历表。这是一个表格，其列代表日期、周、月、年、星期几、节假日、星期等，根据需要进行预填充，并在每个列上进行索引。每年只有365行。

select min(date), max(date)
from calendar
where week = datepart(week, getdate() - 7)
and year = datepart(year, getdate() - 7)

- dean

---

发生的情况是代码将被包含在一个存储过程中，该存储过程将用于水晶报表的自动交付报告。 - asfsdf

Answer 5

一个简短版本的简洁语法

---

上周，在我的视野中是上一个相邻的星期，所以如果我在工作日做报告，我想要收到上一个相邻星期的周一到周日的日期。

• 上一个星期一是DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY)

• 上一个星期日是DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY)

• 然后只需要将这两个集成到SELECT语句中，就可以从表中获取报告了

select * from XXX where DateCol between DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY) and DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY) order by DateCol

- code swimmer

Answer 6

我来晚了，但是想要补充一种在SQL Server中适用于我的更简单的方法（当其他示例不起作用时）。

select * from table

——这是上周的内容：

where (datefield >= DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)) and datefield < DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)))

-- 或者，这是针对滚动的7天：

and DATEDIFF(day, datefield, GETDATE()) between 0 and 7 

- phiphil