Find Friday That Specific Date is “Closest” to T-Sql

Question

I am looking for an elegant, easy way to determine a Friday date that is closest to a specific date. Any ideas?

Answer 1

You need to shift the beginning of the week (using DATEFIRST ) so that you become Tuesday in the middle of the week, and then just add the number of days to go next 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 2

This returns the coming Friday in the future:

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

Answer 3

The trick is to determine how many days are left next Friday from the proposed date. To help, review the whole week and number of days away from next Friday:

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

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

Firstly, on Sunday and Monday. It adds 1 to the value of the day of the week, then takes a negative value to apply to the date of addition. For example, Monday has a default value of 2 as the day of the week value. (2 + 1) * -1 = -3. -3 + Monday = Friday.

Tuesday - Saturday uses the same arithmetic: dates return the values ​​of the day of the week 3, 4, 5, 6, and 7. We need dates that add the values ​​3,2,1,0, -1, respectively. The formula for obtaining it 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 

Answer 4

If you need to find the nearest (past or future) Friday, try the following:

 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 

CONCLUSION:

 DateOf ----------------------- 2010-02-26 00:00:00.000 (1 row(s) affected) 

Answer 5

SQL Server solution as a user-defined function. Will be rounded not only until the next Friday, but also until the next day of the week (1-7), you indicate:

 CREATE FUNCTION RoundToNearestWeekday ( --Give this function a date, and the number of the weekday you want to round to the nearest of @DateInput date, --Date you want to round @ToWeekdayNumber tinyint --1 = round to nearest Sunday, 2 = round to nearest Monday, etc. ) RETURNS date AS BEGIN DECLARE @Offset tinyint, @LowNumber smallint, @HighNumber smallint, @NewDate date SET @Offset = (@ToWeekdayNumber + 3)%7 SET @LowNumber = @Offset-3 SET @HighNumber = @Offset+4 SET @NewDate = dateadd(day,CASE WHEN datepart(weekday,@DateInput) <= @Offset THEN @LowNumber ELSE @HighNumber END - datepart(weekday,@DateInput),@DateInput) RETURN @NewDate END