Find nearest day of week for a given date and day of week

For a pair of dates (date1, date2) = (Fri 2021-01-01, Wed 2020-01-01) I need to extract the day of week from date1 (Friday in this case) then find the nearest Friday (or whatever) for date2.

The result for the above pair would be Fri 2020-01-03 (2 days after that Wednesday) and not Fri 2019-12-27 (5 days before that Wednesday).

There are examples that find previous/next day-of-week, use a hard-coded day of week or use SET DATEFIRST statement; all of which I must avoid. Here is some test data — pair of dates and expected result:

CREATE TABLE #tests (
    date1 DATE,
    date2 DATE,
    result DATE
);

INSERT INTO #tests(date1, date2, result) VALUES
(/*Fri*/ '2021-01-01', /*Wed*/ '2020-01-01', /*Fri*/ '2020-01-03'),
(/*Sat*/ '2021-01-02', /*Thu*/ '2020-01-02', /*Sat*/ '2020-01-04'),
(/*Sun*/ '2021-01-03', /*Fri*/ '2020-01-03', /*Sun*/ '2020-01-05'),
(/*Mon*/ '2021-01-04', /*Sat*/ '2020-01-04', /*Mon*/ '2020-01-06'),
(/*Tue*/ '2021-01-05', /*Sun*/ '2020-01-05', /*Tue*/ '2020-01-07'),
(/*Wed*/ '2021-01-06', /*Mon*/ '2020-01-06', /*Wed*/ '2020-01-08'),
(/*Thu*/ '2021-01-07', /*Tue*/ '2020-01-07', /*Thu*/ '2020-01-09');

INSERT INTO #tests(date1, date2, result) VALUES
(/*Fri*/ '2021-01-01', /*Sun*/ '2017-01-01', /*Fri*/ '2016-12-30'),
(/*Sat*/ '2021-01-02', /*Mon*/ '2017-01-02', /*Sat*/ '2016-12-31'),
(/*Sun*/ '2021-01-03', /*Tue*/ '2017-01-03', /*Sun*/ '2017-01-01'),
(/*Mon*/ '2021-01-04', /*Wed*/ '2017-01-04', /*Mon*/ '2017-01-02'),
(/*Tue*/ '2021-01-05', /*Thu*/ '2017-01-05', /*Tue*/ '2017-01-03'),
(/*Wed*/ '2021-01-06', /*Fri*/ '2017-01-06', /*Wed*/ '2017-01-04'),
(/*Thu*/ '2021-01-07', /*Sat*/ '2017-01-07', /*Thu*/ '2017-01-05');

Answer

Assuming you can create/modify a calendar dimension, is this what you are after?

WITH
DAY_OF_WEEK_CALENDAR AS
  ( SELECT CAST('2021-04-01' AS DATE) AS date_id, 'Thursday' AS day_of_week UNION
    SELECT CAST('2021-04-02' AS DATE), 'Friday' UNION
    SELECT CAST('2021-04-03' AS DATE), 'Saturday' UNION
    SELECT CAST('2021-04-04' AS DATE), 'Sunday' UNION
    SELECT CAST('2021-04-05' AS DATE), 'Monday' UNION
    SELECT CAST('2021-04-06' AS DATE), 'Tuesday' UNION
    SELECT CAST('2021-04-07' AS DATE), 'Wednesday' UNION
    SELECT CAST('2021-04-08' AS DATE), 'Thursday' UNION
    SELECT CAST('2021-04-09' AS DATE), 'Friday' UNION
    SELECT CAST('2021-04-10' AS DATE), 'Saturday' UNION
    SELECT CAST('2021-04-11' AS DATE), 'Sunday' UNION
    SELECT CAST('2021-04-12' AS DATE), 'Monday' UNION
    SELECT CAST('2021-04-13' AS DATE), 'Tuesday' UNION
    SELECT CAST('2021-04-14' AS DATE), 'Wednesday'
  ),
  TEST AS 
  ( SELECT CAST('2021-04-02' AS DATE) AS date_1,
           CAST('2021-04-10' AS DATE) AS date_2
      UNION
    SELECT CAST('2021-04-04' AS DATE) AS date_1,
           CAST('2021-04-10' AS DATE) AS date_2
  )
SELECT TEST.*,
       ( SELECT TOP 1 date_id
           FROM DAY_OF_WEEK_CALENDAR DOW_2
          WHERE DOW_2.date_id BETWEEN DATEADD(day,-7,TEST.date_2)
                   AND DATEADD(day,7,TEST.date_2)
            AND DOW_1.day_of_week = DOW_2.day_of_week
          ORDER
             BY DATEDIFF(day,DOW_2.date_id,TEST.date_2) ASC
       ) AS nearest_date_match
  FROM TEST
 INNER
  JOIN DAY_OF_WEEK_CALENDAR DOW_1
    ON TEST.date_1 = DOW_1.date_id