Find the Friday that a particular date is "closest" to in T-Sql

Question

I'm looking for an elegant simple way to determine the date of the Friday that is closest to a particular date. Any ideas?

Answer 1

This returns a closest Friday in the future:

SELECT  DATEADD(day, 6 - (DATEDIFF(day, '01/01/2010', @mydate) - 1) % 7, @mydate)

Answer 2

You have to slide the beginning of the week (using DATEFIRST) so that you get Tuesday becomes the middle of the week and then you just add the number of days to go to the closest Friday,.

SET NOCOUNT ON
SET DATEFIRST 3

Declare @DateValue DateTime

SET @DateValue = '1/1/2010'

While @DateValue < '2/1/2011'
BEGIN
    PRINT DateAdd (Day, 3 - DatePart (dw, @DateValue), @DateValue)
    SET @DateValue = @DateValue + 1
END

Answer 3

if you need to find the closest (past or future) Friday, try this:

DECLARE @StartDate datetime
       ,@EndDate   datetime
       ,@BeforeDate   datetime


SET @StartDate='2010-3-1'---<<<given date, Monday, closest should be '2010-2-26'
SET @EndDate=@StartDate+8
SET @BeforeDate=@StartDate-8
;with AllDates AS
(
    SELECT @StartDate AS DateOf, 1 as TypeOf,DATENAME(weekday,@StartDate) AS WeekDayOf, ABS(DATEDIFF(day,@StartDate,@StartDate)) AS DifferenceOf
    UNION ALL
    SELECT DateOf+1 AS DateOf,2 AS TypeOf,DATENAME(weekday,DateOf+1 ) AS WeekDayOf, ABS(DATEDIFF(day,@StartDate,DateOf+1)) AS DifferenceOf
        FROM AllDates
    WHERE DateOf<@EndDate-1 AND TypeOf IN (1,2)
    UNION ALL
    SELECT DateOf-1 AS DateOf,3 AS TypeOf,DATENAME(weekday,DateOf-1 ) AS WeekDayOf, ABS(DATEDIFF(day,@StartDate,DateOf-1)) AS DifferenceOf
        FROM AllDates
    WHERE DateOf>@BeforeDate-1 AND TypeOf IN (1,3)
)
SELECT TOP 1 DateOf
    FROM AllDates
    WHERE WeekDayOf='Friday'
    ORDER BY DifferenceOf

OUTPUT:

DateOf
-----------------------
2010-02-26 00:00:00.000

(1 row(s) affected)

Answer 4

The trick is to determine how many days away the closest Friday is from the offered date. To help, look at an entire week and the number of days away from the closest Friday:

Sunday -2
Monday -3
Tuesday 3
Wednesday 2
Thursday 1
Friday 0
Saturday -1

Now you need a formula to return these results. Because Sunday and Monday follow a different pattern from the other days of the week, two formulas are needed.

First, here is the one for Sunday and Monday. It adds 1 to the day of the week value then takes the negative to apply to the date add. For example, Monday has a default of 2 as the day of the week value. (2 + 1) * -1 = -3. -3 + Monday = Friday.

Tuesday - Saturday use similar arithmetic: The dates return the day of week values 3, 4, 5, 6, & 7. We need date add values of 3,2,1,0,-1 respectively. The formula to get this is DW * -1 + 6.

DECLARE @Date AS datetime
SET @Date = '3/1/2010'
SELECT
    CASE
        WHEN DATEPART(dw, @Date) <= 2
        THEN DATEADD(d, -1 * (DATEPART(dw, @Date) + 1), @Date)
        ELSE DATEADD(d, DATEPART(dw, @Date) * -1 + 6, @Date)
    END AS NearestFriday