获取最近星期五的日期 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5998084/
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
Get the most recent Friday's date SQL
提问by James
I'm trying to get the most recent Friday in SQL Server 2008.
我正在尝试获取 SQL Server 2008 中最近的星期五。
I have this. It gets the beginning of the week (monday) then subtracts 3 days to get Friday.
我有这个。它获得一周的开始（星期一），然后减去 3 天得到星期五。
declare @recentFriday datetime = DATEADD(ww, DATEDIFF(dd,0,GETDATE()), 0)-3
When this is run during the week, it gets last Friday's date which is correct. But when run on Friday (or Saturday), it still gets last week's date instead of the current week's Friday. I'm about to use if/else conditions but I'm sure there's an easier way.
当它在一周内运行时，它会得到上周五的日期，这是正确的。但是当在星期五（或星期六）运行时，它仍然获得上周的日期而不是当前周的星期五。我即将使用 if/else 条件，但我确信有更简单的方法。
采纳答案by mellamokb
This works for any input and any setting of DATEFIRST:
这适用于任何输入和任何设置DATEFIRST：
dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())
It works by adjusting the weekday value so that 0 = Friday, simulating Friday as the beginning of the week. Then subtract the weekday value if non-zero to get the most recent Friday.
它的工作原理是调整工作日值，使 0 = 星期五，模拟星期五作为一周的开始。如果非零，则减去工作日值以获得最近的星期五。
Edit: Updated to work for any setting of DATEFIRST.
编辑：更新为适用于DATEFIRST.
回答by Pero P.
DECLARE @date DATETIME = '20110512' -- Thursday
SELECT DATEADD(DAY,-(DATEDIFF(DAY,'19000105',@date)%7),@date) --20110506
SET @date = '20110513' -- Friday
SELECT DATEADD(DAY,-(DATEDIFF(DAY,'19000105',@date)%7),@date) --20110513
SET @date = '20110514' -- Saturday
SELECT DATEADD(DAY,-(DATEDIFF(DAY,'19000105',@date)%7),@date) --20110513
- Calculate the number of days between a known Friday (05 Jan 1900) and the given date
- The remainder left from dividing the difference in 1. by 7 will be the days elapsed since the last Friday
- Subtract the remainder in 2. from the given date
- 计算已知星期五（1900 年 1 月 5 日）和给定日期之间的天数
- 将差值除以 7 所得的余数将是自上周五以来经过的天数
- 从给定的日期减去 2. 中的余数
回答by RRUZ
you can check if the current day of week is friday or greater DATEPART(dw,GETDATE())and then call (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4)or (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)-3)
您可以检查当前星期几是否是星期五或更晚， DATEPART(dw,GETDATE())然后致电(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4)或(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)-3)
SELECT
CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN
(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4)
ELSE
(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)-3)
END
回答by Joe Stefanelli
Using a known Friday date (I'll use Jan 7, 2011) as a starting point, you can do this:
使用已知的星期五日期（我将使用 2011 年 1 月 7 日）作为起点，您可以执行以下操作：
DECLARE @d DATETIME
SET @d = '2011-05-13' /* Friday */
SELECT DATEADD(DAY, (DATEDIFF (DAY, '20110107', @d) / 7) * 7, '20110107')
/* Returns 2011-05-13 */
SET @d = '2011-05-12' /* Thursday */
SELECT DATEADD(DAY, (DATEDIFF (DAY, '20110107', @d) / 7) * 7, '20110107')
/* Returns 2011-05-06 */
Just choose a known Friday that's older than any dates you'll be using in your calculations.
只需选择一个比您将在计算中使用的任何日期早的已知星期五。
回答by Scott Bruns
Here is a completly set orientedway to achive the last Friday:
这是实现最后一个星期五的完全面向方式的方法：
select Friday from
(
select max(GetDate()) as Friday where datepart(dw, getdate()) = 6
union all
select max((GetDate() - 1)) where datepart(dw, (getdate() - 1)) = 6
union all
select max((GetDate() - 2)) where datepart(dw, (getdate() - 2)) = 6
union all
select max((GetDate() - 3)) where datepart(dw, (getdate() - 3)) = 6
union all
select max((GetDate() - 4)) where datepart(dw, (getdate() - 4)) = 6
union all
select max((GetDate() - 5)) where datepart(dw, (getdate() - 5)) = 6
) x where Friday is not null
回答by suman
SELECT CONVERT(VARCHAR(12),GETDATE()) AS Today,
CASE WHEN (DATEPART(DW,GETDATE())< 7)
THEN CONVERT(VARCHAR(12),(DATEADD(dd,-(DATEPART(DW,GETDATE())+1),GETDATE())))
ELSE CONVERT(VARCHAR(12),(DATEADD(d,- 1,GETDATE())))
END AS [Last Friday]
回答by user8994306
The other solutions were not working for my use case.
其他解决方案不适用于我的用例。
This works for finding any previous day by replacing 'Sunday' with the day you`re looking for:
这适用于通过将“星期日”替换为您要查找的日期来查找前一天：
DECLARE @myDate DATE = GETDATE()
WHILE DATENAME(WEEKDAY, @myDate) <> 'Sunday'
BEGIN
SET @myDate = DATEADD(DAY, -1, @myDate)
END
