creating calendar with non default start and end year dates in sql

I need to create year calendar that starts and ends on specific date. It does not start on 1st january and does not end on 31st december but rather on dates that can vary from year to year (I declare/set them programatically). It is the calendar for school (school year). Currently for next year it will begin on 1st October and end on 30th September. What I need is sequential number of week开发者_如何学JAVA, week begin and week end date and all dates in between.

So for first week of 2011/2012 school year (1st October - 30th September) I would get week number = 1, week start date = 2011-10-01 and week end date = 2011-10-02 (basically two rows for each day in that week) etc.

The problem that I have is that when using native Microsoft Date and Time functions I only get for example week number in "default year range":

DATEPART(ww, [Date])

The upper if I pass in 1st October will return 40. But my result should be 1.

Any suggestion how to do this? What I currently have is table with all dates for years (default years from 1990 until 2100), default week numbers and months in it. I was thinking of selecting date ranges (that would be my school year start and end date) from it and assigning proper week dates and start and end dates somehow.

And no this is not some school project or homework it is for my work:)

After reading your question again i came up with this instead

declare @start datetime
declare @end datetime

set @start = '2011-10-01'
set @end = '2012-9-30'

;with cte as
(
select @start firstday, @start + 6 - DATEDIFF(day, 0, @start) %7 lastday,  1 week
union all
select lastday + 1, case when @end < lastday + 7 then @end else lastday + 7 end,  week + 1
from cte
where lastday < @end
)
select cast(firstday as date) firstday, cast(lastday as date) lastday, week  from cte
option(MAXRECURSION 0) 

Result:

firstday   lastday    week
---------- ---------- ----
2011-10-01 2011-10-02 1
2011-10-03 2011-10-09 2
2011-10-10 2011-10-16 3
2011-10-17 2011-10-23 4
....
2012-09-17 2012-09-23 52
2012-09-24 2012-09-30 53

Old surgestion

declare @start datetime
declare @end datetime

set @start = '2011-10-01'
set @end = '2012-9-30'

;with cte as
(
select @start calendardate, 1 week
union all
select calendardate + 1, week + case when DATEDIFF(day, 0, calendardate) %7 = 6 then 1 else 0 end
from cte
where calendardate < @end
)
select cast(calendardate as date) calendardate, week  from cte
option(MAXRECURSION 0) 

Result:

calendardate week
------------ -----------
2011-10-01   1
2011-10-02   1
2011-10-03   2
2011-10-04   2
2011-10-05   2
2011-10-06   2
2011-10-07   2
2011-10-08   2
2011-10-09   2
2011-10-10   3
2011-10-11   3
2011-10-12   3
.....
2012-09-29   53
2012-09-30   53

Answer of [t-clausen.dk] is good enough, but you will need EndOfWeek and BeginOf Week:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = '2011-10-01'
SET @end = '2012-9-30';

WITH cte(calendardate, week, beginofweek, endofweek)
     AS (SELECT @start         calendardate,
                CAST(1 AS INT) week,
                Dateadd(DAY, 0, Datediff(DAY, 0, @start) -
                                Datediff(DAY, 0, @start) %
                                7)
                               beginofweek,
                Dateadd(DAY, 6, Datediff(DAY, 0, @start) -
                                Datediff(DAY, 0, @start) %
                                7)
         UNION ALL
         SELECT calendardate + 1,
                week + CASE
                         WHEN Datediff(DAY, 0, calendardate) %7 = 6 THEN 1
                         ELSE 0
                       END,
                Dateadd(DAY, 0, Datediff(DAY, 0, calendardate + 1) -
                                Datediff(DAY, 0, calendardate + 1) % 7)
                beginofweek,
                Dateadd(DAY, 6, Datediff(DAY, 0, calendardate) -
                                Datediff(DAY, 0, calendardate) % 7)
         FROM   cte
         WHERE  calendardate < @end)
SELECT CAST(calendardate AS DATE) calendardate,
       week,
       CAST(beginofweek AS DATE)  beginofweek,
       CAST(endofweek AS DATE)    endofweek
FROM   cte
OPTION( MAXRECURSION 0) 

Result:

calendardate week        beginofweek endofweek
------------ ----------- ----------- ----------
2011-10-01   1           2011-09-26  2011-10-02
2011-10-02   1           2011-09-26  2011-10-02
2011-10-03   2           2011-10-03  2011-10-02
2011-10-04   2           2011-10-03  2011-10-09
2011-10-05   2           2011-10-03  2011-10-09
2011-10-06   2           2011-10-03  2011-10-09
2011-10-07   2           2011-10-03  2011-10-09
2011-10-08   2           2011-10-03  2011-10-09
2011-10-09   2           2011-10-03  2011-10-09
2011-10-10   3           2011-10-10  2011-10-09
2011-10-11   3           2011-10-10  2011-10-16
2011-10-12   3           2011-10-10  2011-10-16
2011-10-13   3           2011-10-10  2011-10-16
2011-10-14   3           2011-10-10  2011-10-16
2011-10-15   3           2011-10-10  2011-10-16
2011-10-16   3           2011-10-10  2011-10-16
2011-10-17   4           2011-10-17  2011-10-16
2011-10-18   4           2011-10-17  2011-10-23

...

2012-09-21   52          2012-09-17  2012-09-23
2012-09-22   52          2012-09-17  2012-09-23
2012-09-23   52          2012-09-17  2012-09-23
2012-09-24   53          2012-09-24  2012-09-23
2012-09-25   53          2012-09-24  2012-09-30
2012-09-26   53          2012-09-24  2012-09-30
2012-09-27   53          2012-09-24  2012-09-30
2012-09-28   53          2012-09-24  2012-09-30
2012-09-29   53          2012-09-24  2012-09-30
2012-09-30   53          2012-09-24  2012-09-30