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

asfsdf 发表于 Dev

asfsdf

我在Crystal报表中有这段代码，该代码可以根据当前日期提供上周的日期范围。

一周的第一天：

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

如何在SQL中使用2个变量存储Monday（startdate）和Sunday（enddate）来做同样的事情？

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

比尔·斯蒂达姆（Bill Stidham）

我在parmsCTE中生成了一些间隔的日期，然后SELECT是CurrentDate从parms之前的一周的周日和之前CurrentDate的周六的日期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))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。