SQL 如何在sql中获得上周的最后一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5924777/
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 day of last week in sql?
提问by James123
How to get last date of the lastweek in sql? I mean last sunday date using query?
如何在sql中获取上周的最后日期？我的意思是使用查询的最后一个星期天日期？
回答by Andriy M
Regardless of the actual DATEFIRST setting, the last Sunday could be found like this:
不管实际的 DATEFIRST 设置如何，最后一个星期日都可以这样找到：
SELECT DATEADD(day,
-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,
GETDATE()
) AS LastSunday
Replace GETDATE()with a parameter @dateto get the last Sunday before a particular date.
替换GETDATE()为参数@date以获取特定日期之前的最后一个星期日。
回答by beach
Last Sunday(Which is the end of "last week")
上周日（即“上周”的结束）
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS LAST_SUNDAY
This Week(Assuming Mon-Sun Week Format)
本周（假设周一至周日的周格式）
SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS START_OF_WEEK
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) AS END_OF_WEEK
Results
结果
START_OF_WEEK
-----------------------
2011-05-02 00:00:00.000
END_OF_WEEK
-----------------------
2011-05-08 00:00:00.000
Examples to explain the voodoo(Use this to change above SQL to your desired Week Starting and Week Ending day-of-week)
解释伏都教的示例（使用此将上述 SQL 更改为您想要的周开始和周结束日）
- The examples below locate days of the week within the current week (Sunday to Saturday)
- If the actual END_OF_WEEK is next Sun-Sat week, then you need to +7 to this week's value. (See the END_OF_WEEK example above.)
- 下面的示例在当前周（周日到周六）中定位一周中的几天
- 如果实际的 END_OF_WEEK 是下一个周日至周六周，那么您需要将本周的值加 7。（请参阅上面的 END_OF_WEEK 示例。）
SQL Below
下面的 SQL
SELECT DATEADD(wk, DATEDIFF(wk, -2, CURRENT_TIMESTAMP), -2) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 0, CURRENT_TIMESTAMP), 0) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 1, CURRENT_TIMESTAMP), 1) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 2, CURRENT_TIMESTAMP), 2) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 3, CURRENT_TIMESTAMP), 3) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 4, CURRENT_TIMESTAMP), 4) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 5, CURRENT_TIMESTAMP), 5) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 8, CURRENT_TIMESTAMP), 8) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 9, CURRENT_TIMESTAMP), 9) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 10, CURRENT_TIMESTAMP), 10) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 11, CURRENT_TIMESTAMP), 11) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 12, CURRENT_TIMESTAMP), 12) AS DAY_OF_WEEK /* Saturday */
etc...
回答by IAmTimCorey
Here is a great article on how to do this:
这是一篇关于如何做到这一点的好文章：
http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx
http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx
You would want to use the @StartOfPrevWeek variable.
您可能想要使用 @StartOfPrevWeek 变量。
回答by Tom
DECLARE @LastSunday DATETIME
-- This will get the previous Sunday with time as 23:59:59
SELECT @LastSunday = Dateadd(SECOND, -1, Dateadd(WK, Datediff(WK, 6,
CURRENT_TIMESTAMP)
, 7))
SELECT @LastSunday
-- This gets the monday prior to it and time of 00:00:00
SELECT Dateadd(SECOND, 1, Dateadd(DAY, -7, @LastSunday))
-- This will make you time spans between eg, Monday 16/07/2012 00:00:00 through to Sunday 22/07/2012 23:59:59
-- Then use them in your WHERE clause like this
-- SELECT X,Y,Z From SomeTable
-- WHERE DateField BETWEEN @PreviousMondayToLastSunday AND @LastSunday
回答by FistOfFury
To get the previous sunday, or today if today is sunday, try this
要获取上一个星期日，或者今天如果今天是星期日，请尝试此操作
DATEADD(day,- (DATEPART(dw,getdate()) + @@DATEFIRST -1) % 7, getdate())
回答by Mina Gabriel
This will get you the next and precious Friday from a given date and time
这将使您从给定的日期和时间获得下一个宝贵的星期五
DECLARE @PREVIOUS int, @dtmStart datetime,@dtmEnd datetime, @NEXT int;
SET @dtmStart = '12/10/2013';
SET @dtmEnd = '12/11/2013';
select @PREVIOUS = datepart(dw,@dtmStart)
WHILE @PREVIOUS <> 6
BEGIN
SET @dtmStart = DATEADD(day , -1 ,@dtmStart)
SET @PREVIOUS = datepart(dw,@dtmStart)
CONTINUE
END
select @dtmStart
SELECT @NEXT = DATEPART(dw, @dtmEnd)
WHILE @NEXT <> 6
BEGIN
SET @dtmEnd = DATEADD(day , 1 ,@dtmEnd)
SET @NEXT = datepart(dw,@dtmEnd)
CONTINUE
END
select @dtmEnd
回答by Mike Sherrill 'Cat Recall'
The SQL is more straightforward with a suitable calendar table. No voodoo.
使用合适的日历表，SQL 会更直接。没有伏都教。
select max(cal_date) end_of_last_week
from calendar
where (cal_date < current_date and day_of_week = 'Sun');
end_of_last_week
--
2011-05-01
回答by Mark W
SELECT (DATEADD(DAY, ((DATEPART(dw, @Date) - 1) * -1), @Date))
回答by Mayuran Parathalingam
Here is the code to get the date of last Saturday. This method is independent of settings of the database.
这是获取上周六日期的代码。此方法与数据库的设置无关。
declare @lastSaturday date,
@today date,
@todayName varchar(20);
select @todayName = datename(weekday, getdate()), @today = getdate();
select
case @todayName
when 'Saturday' then @today
when 'Sunday' then dateadd(day,-1,@today)
when 'Monday' then dateadd(day,-2,@today)
when 'Tuesday' then dateadd(day,-3,@today)
when 'Wednesday' then dateadd(day,-4,@today)
when 'Thursday' then dateadd(day,-5,@today)
when 'Friday' then dateadd(day,-6,@today)
end as LastSaturday;
回答by David Fells
SET @EndDate = GETDATE()-DatePart(dw, GETDATE());
