Find days of week interval including a given date

get-date-interval.sql

/*
Get date interval

Find days of week interval including @date.

Args:
    @date   -- Date to find interval
    @first  -- Interval start (day of week number where 1 is Mon, 7 is Sun)
    @last   -- Interval start (day of week number where 1 is Mon, 7 is Sun)

Returns TV: (
    [date]   -- Current date
    [first]  -- Begin of interval 
    [last]   -- End of interval
)

Examples:
    E. g. we need to build a report from Friday to Thursday around the current date:
        select * from dbo.getInterval(default, 5, 4) 
 
    Or from Thursday to Sunday:
        select * from dbo.getInterval(default, 4, 7)    

    Or from Mon to Fri around a June, 18 2019:
        select * from dbo.getInterval('2019-06-18', 1, 5) 
*/

drop function if exists dbo.getInterval;
go

create function dbo.getInterval(@date date = null, @first smallint = 1, @last smallint = 7)
returns @result table (
    [date] date
  , [first] date
  , [last] date
)
as
begin
    if @date is null begin
        set @date = getdate();  
    end;

    declare @dw smallint = datepart(dw, @date);

    insert @result ([date], [first], [last])
        select
            @date
          , dateadd(dd, IIF(@dw >= @first, @first - @dw, @first - @dw - 7), @date)
          , dateadd(dd, IIF(@dw <= @last, @last - @dw, @last - @dw + 7), @date)
    ;

    return;
end;
go