SQL 如何根据sql中的当前日期获取上周日期范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22769986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get last week date range based on current date in sql?
提问by asfsdf
I have this code in crystal reports that gives me last week date range based on the current date.
我在水晶报告中有这个代码，它根据当前日期为我提供上周的日期范围。
First day of the week:
一周的第一天：
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)
Last day of week:
一周的最后一天：
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
How can I do the same in SQL using 2 variables to storage Monday(startdate) and Sunday(enddate)?
如何在 SQL 中使用 2 个变量来存储 Monday( startdate) 和 Sunday( enddate)来做同样的事情？
I found this select datepart(dw,getdate()) --6in this site, but I do not know how to use it.
我select datepart(dw,getdate()) --6在这个网站上找到了这个，但我不知道如何使用它。
回答by Bill Stidham
I generated some spaced out dates in the parmsCTE then SELECTthe CurrentDatefrom parms, the Sunday of the week prior to CurrentDateand the Saturday of the week prior to CurrentDate. I'm assuming that you want the dtate range to be Sunday - Saturday.
我在parmsCTE 中生成了一些间隔日期，然后SELECT是CurrentDatefrom parms、前一周的星期日和前一周CurrentDate的星期六CurrentDate。我假设您希望 dtate 范围是周日 - 周六。
Sunday - Saturday Ranges
周日至周六范围
;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
Monday to Sunday Ranges
周一至周日范围
;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
If you just want the prior week's Monday to the prior week's Sunday from today rather than from a column of dates you can use this
如果您只想从今天开始前一周的星期一到前一周的星期日，而不是从日期列中，您可以使用它
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))
回答by Rafael
This solution is tested and works. I am getting the previous week's Monday and Sunday as upper and lower bounds.
此解决方案经过测试并有效。我将前一周的周一和周日作为上限和下限。
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.
Which can be used like this in a real world query:
在现实世界的查询中可以这样使用：
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')
Here are some tests:
以下是一些测试：
1. Leap Year
1.闰年
Current Date: 2016-02-29 00:00:00.000
当前的日期： 2016-02-29 00:00:00.000
Results:
结果：
LowerLimit UpperLimit
2016-02-22 00:00:00.000 2016-02-28 00:00:00.000
2. Last Week was in different year
2. 上周在不同的年份
Current Date: 2016-01-06 00:00:00.000
当前的日期： 2016-01-06 00:00:00.000
LowerLimit UpperLimit
2015-12-28 00:00:00.000 2016-01-03 00:00:00.000
3. Lower limit in previous month and upper limit in current month
3. 上月下限和当月上限
Current Date: 2016-05-04 00:00:00.000
当前的日期： 2016-05-04 00:00:00.000
LowerLimit UpperLimit
2016-04-25 00:00:00.000 2016-05-01 00:00:00.000
4. Current Date is Sunday
4. 当前日期是星期日
Current Date: 2016-05-08 00:00:00.000
当前的日期： 2016-05-08 00:00:00.000
LowerLimit UpperLimit
2016-04-25 00:00:00.000 2016-05-01 00:00:00.000
回答by blugecko
Bill's code worked well, but I needed to make a slight edit if I wanted to get last Sunday to Saturday based on today's date.
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
Rgds - 马特
回答by dean
For everything date and date interval related, I'd almost always recommend using a calendar table. A table with columns representing date, week, month, year, day of week, hiliday, weekand, etc, prepopulated as needed and indexed on every column. It's only 365 rows per year.
对于与日期和日期间隔相关的所有内容，我几乎总是建议使用日历表。包含表示日期、周、月、年、星期几、hiliday、weekand 等的列的表，根据需要预先填充并在每一列上建立索引。每年只有 365 行。
select min(date), max(date)
from calendar
where week = datepart(week, getdate() - 7)
and year = datepart(year, getdate() - 7)
