How can I find how many Week Numbers are in a given interval of time in T-SQL

Question

I am a bit stuck here, I want to find all the week numbers in a given interval of time and I can't really figure it out

For example , instead of - > datepart(week,dateadd(day,-1,@oneSingleDate)) (monday is the first day of the week) ,

I need something like

- > datepart(week,@startDate,@endDate)

Given the interval '2019-01-04'-'2019-01-28' the output needs to be :

WeekNo : 1,2,3,4,5 .

Possible duplicate of [SQL Server 2008 Generate a Series of date times](https://stackoverflow.com/questions/7149271/sql-server-2008-generate-a-series-of-date-times) — Raymond Nijland, Jun 14 '19 at 09:14
Join against a calendar table with a between and retrieve the week numbers. — EzLo, Jun 14 '19 at 09:14
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — Raymond Nijland, Jun 14 '19 at 09:14
Simply combine both duplicated questions/answer for a query which will give you the correct resultset, in SQL Server 2017 you can use [STRING_AGG()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017) instead of `STUFF` — Raymond Nijland, Jun 14 '19 at 09:15

score 0 · Accepted Answer · answered Jun 14 '19 at 09:32

i've used a recursive CTE to generate all the dates in the range, then I've selected the DISTINCT week numbers from them using DATEPART. Then I've concatenated then into your comma-separated string into a variable called @OUT

DECLARE @startDate as date = '20190104';
DECLARE @endDate as date = '2019-01-28';

DECLARE @OUT as nvarchar(max);

WITH CTE AS (SELECT @startDate As X
             UNION ALL
             SELECT DATEADD(y, 1, X) X FROM CTE where x < @endDate),
    CTE2 AS (SELECT DISTINCT datepart(wk, X) wk from cte)
    select @out = coalesce(@out + ', ', '') + cast(wk as nvarchar(4))  from cte2
    OPTION (MAXRECURSION 0);

    select @out;

Kemal AL GAZZAH · Answer 2 · 2019-06-14T10:01:23.940

You can do it as follows: using : - [CTE][1] common Table expression - [Sql recursion union all][2] - [Concatenation of multiple rows into one line][3]

declare @startDate as date ;
declare @endDate as date ;
set @startDate='2019-01-04'; 
set @endDate='2019-01-28' ;
DECLARE @weeks VARCHAR(8000) ;
with cte as (select @startDate as mydate,datepart(week,@startDate) w
union all select
dateadd(day,1,mydate),datepart(week,dateadd(day,1,mydate))  from cte
where mydate < @endDate) , cte2 as (select distinct(w) from cte)
select @weeks=COALESCE(@weeks + ', ', '') +cast(w as varchar(2)) from
cte2 OPTION (MAXRECURSION 360) select @weeks [Result]

How can I find how many Week Numbers are in a given interval of time in T-SQL

2 Answers2