Arithmetic overflow error converting expression to data type datetime sql

ROW_NUMBER() returns a bigint value but adding days to a date is limited to int; so if the number of rows produced by the cross join exceeds the maximum value of an int you will produce that error message.

The number of rows in sys.columns only needs to exceed the square root of 2,147,483,647 (>=46,341) for the Cartesian product used in your code to produce that error. I was able to reproduce the same error on a much smaller sys.columns table by repeating the cross joins. e.g:

  SELECT 
      @start_date + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) dt
  FROM 
      sys.columns s1 
  CROSS JOIN
      sys.columns s2
  CROSS JOIN
      sys.columns s3
  CROSS JOIN
      sys.columns s4

One way to avoid the problem would be instead of adding row_number() to a date in the inner query, just return row_number() instead and use the where clause to avoid the error, e.g:

DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE @Table TABLE (StartDate DATETIME, Enddate DATETIME, WeekNo INT)

SET @start_date = '2017-01-01'
SET @end_date =   '2017-12-31'

INSERT INTO @Table 
    SELECT 
        MIN(dt), dateadd(day,7,MIN(dt)), w
    FROM
        (SELECT 
             dateadd(day,q.i,@start_date) dt, year(dateadd(day,q.i,@start_date)) y, DATEPART(week, dateadd(day,q.i,@start_date)) w
         FROM (
             SELECT 
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 i
              FROM 
                  sys.columns s1 
              CROSS JOIN
                  sys.columns s2
             ) q
         WHERE 
             i <= datediff(day,@start_date,@end_date) 
         ) q2
GROUP BY 
    y, w
;

Note there are many alternatives, particularly as you are really only want week ranges. You could use a permanent "tally table" instead of sys.columns, or use repeated cross joins of a 10 row CTE to create the wanted rows.

The accepted answer to this question Tally Table to insert missing dates between two dates? SQL is an example of using a permanent table of numbers (aka "tally table"). Also note by doing this you could add 7 days per row thus avoiding the need to group the days into weeks.

Here is an example using a repeated cross join of a CTE:

DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE @Table TABLE (StartDate DATETIME, Enddate DATETIME, WeekNo INT)

SET @start_date = '2017-01-01'
SET @end_date =   '2017-12-31'

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS i
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT
      dateadd(day,q.i*7,@start_date) startDate
    , dateadd(day,(q.i*7)+7,@start_date) endDate
    , year(dateadd(day,q.i*7,@start_date)) y
    , DATEPART(week, dateadd(day,q.i*7,@start_date)) w

FROM cteTally q
WHERE dateadd(day,(q.i*7)+7,@start_date) <= @end_date
ORDER BY q.i