获取最近的周五日期SQL

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条件，但我肯定有更简单的方法。

6 个解决方案

#1

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.

编辑:更新为任何日期设置的工作。

#2

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
计算一个已知的星期五(1900年1月5日)和指定日期之间的天数。
The remainder left from dividing the difference in 1. by 7 will be the days elapsed since the last Friday
余数除以1。从上星期五算起，从7日算起
Subtract the remainder in 2. from the given date
减去2的余数。从给定的日期

#3

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)

您可以检查当前的星期是星期五还是更大的日期部分(dw,GETDATE()))，然后调用(选择DATEADD(wk, DATEDIFF(wk,0,GETDATE()，0)+4)或(选择DATEADD(wk, DATEDIFF,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

#4

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.

选择一个已知的星期五，这个日期比你在计算中使用的任何日期都要早。

#5

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]

#6

Here is a completly set oriented way 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

#1