How to get a list of Week Start Date (Monday) and

问题:

I am just finding a way to get a list of Start Week and End Week between two date range.

For e.g. if I call a function or a stored procedure say

GetWeekDates ('21 Mar 2014','21 Mar 2014')

Then I should get a table as:

Start Week | End Week

17 March 2014 | 23 Mar 2014
24 March 2014 | 30 Mar 2014
....

I don't want to use CURSOR or WHILE Loop.

Any direction where I should lead to?

I am just doing a re factor on existing code.

Kind regards, Am

回答1:

This will work as long as the intervals are 38 years or less. It will offer better performance and it does not rely on the local setting of the server.

This setting will cause your script to return wrong result:

set datefirst 1
select * from dbo.fnGetWeeksBetweenDates('2014-03-21','2014-03-21')

This means your local setting currently conflicts with your needs and your code is compensating.

Here is the script. The script is limited to 38 years for performance reasons (I find it unlikely that you need bigger intervals than that). It will be fairly easy to extend it to more years.

DECLARE @FromDate DATE = '2014-03-21'    
DECLARE @ToDate DATE  = '2014-03-24'    

SELECT @fromdate = dateadd(day, datediff(day, 0, @FromDate)/7*7, 0), 
@todate = dateadd(day, datediff(day, 0, @ToDate)/7*7, 6)

SELECT dateadd(d, number * 7, @fromdate) Start_Week, 
dateadd(d, number * 7 + 6, @fromdate) End_Week
FROM
master..spt_values
WHERE type = 'P' and
@todate >= dateadd(d, number * 7, @fromdate)

Result:

Start_Week  End_Week
2014-03-17  2014-03-23
2014-03-24  2014-03-30

回答2:

I end up going through WITH statement and it is much faster than any other way; refactor mission accompliseded

CREATE FUNCTION fnGetWeeksBetweenDates
(
    @FromDate DATETIME,    
    @ToDate DATETIME
) RETURNS @tbl TABLE (StartWeek date, EndWeek date)
AS BEGIN

    DECLARE @TOTALWEEK INT
    SET @FromDate = DATEADD(DAY,-1*DATEPART(dw,@FromDate)+2,@FromDate)
    SET @TOTALWEEK = DATEDIFF(ww,@FromDate,@ToDate)+1;

    WITH weekdays (StartWeek, EndWeek) as 
    (
        select top (@TOTALWEEK) StartWeek = DateAdd(Week, ROW_NUMBER()
            OVER (ORDER BY object_id)-1, @FromDate),
            EndWeek =  DateAdd(Week, ROW_NUMBER()
            OVER (ORDER BY object_id)-1, @FromDate)
        FROM SYS.all_objects
    )

    insert into @tbl
    select StartWeek, DATEADD(day, 6, EndWeek) as EndWeek  From weekdays;

    RETURN
END;

Here is how you can call:

select * from dbo.fnGetWeeksBetweenDates('21 MAR 2014', '21 MAR 2015')