CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
immutabl
immutabl

Reputation: 6903

How to generate formatted week strings from start and end dates

Is it possible to generate a set like this:

April 1 2010  
April 8 2010  
April 15 2010  
April 22 2010  
April 29 2010  
May 6 2010

and so on.

from a startdate and an end date using only SQL?

Upvotes: 0

Views: 106

Answers (1)

Alex K.
Alex K.

Reputation: 175766

You can recurse over a CTE:

declare @start datetime = '1 apr 2010'
declare @end   datetime = '6 may 2010'

;with weeks(wdate) as
(
    select @start as wdate

    union all

    select 
        dateadd(week, 1, wdate)
    from weeks
        where wdate < @end
)
select wdate
    from weeks 

2010-04-01 00:00:00.000
2010-04-08 00:00:00.000
2010-04-15 00:00:00.000
2010-04-22 00:00:00.000
2010-04-29 00:00:00.000
2010-05-06 00:00:00.000

Upvotes: 3

Related Questions