What is the best way to get start date and end date of the week between two dates in SQL

Question

I work with SQL Server 2005. I have a start and end date.

Ex:

START_DATE    END_DATE
2011-04-24    2012-05-05 

I want to get all the start day (SQL is set so that it's sunday) and end day (saturday) of each week between the two dates.

Desired result :

2011-04-24    2011-04-30
2011-05-01    2011-05-07
2011-05-08    2011-05-14
....
2012-04-29    2012-05-05

Now I know I could do something like DATEADD(week, 1, 2011-04-24) until I get to my end date, but I don't quite see how to do it until I get to my end date (without having a cursor or a while). I would like to do it in one query.

Can anyone help?

Answer 1

Just found a way to do it with recursivity!

;WITH DaysOfWeek AS 
( 
    SELECT {d '2011-04-24'} AS StartOfWeek
    UNION ALL 
    SELECT DATEADD( week, 1, jdr.StartOfWeek) AS StartOfWeek
    FROM JoursDuReleve jdr 
    WHERE DATEADD(week, 1, jdr.StartOfWeek) <= {d '2012-05-05'}
) 
SELECT StartOfWeek, DATEADD(day, 6, StartOfWeek) AS EndOfWeek  
FROM DaysOfWeek
ORDER BY StartOfWeek DESC 
OPTION (MAXRECURSION 0)

Answer 2

Using a date function is a smart and forward thinking option. A great one is F_TABLE_DATE over on sqlteam.

I extracted the relevant piece of the code to illustrate how you might leverage as a one-off:

declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2011-04-24',
        @eDate = '2012-05-05';

;with AllDatesBetween (d)
as  (   
        select dateadd(dd, n-1, @sDate)
        from    util.Number
        where   n <= datediff(dd, @sDate, @eDate+1)
    )
select  distinct
        dateadd(dd,(datediff(dd,'17530107',d)/7)*7,'17530107') [weekStart],
        dateadd(dd,((datediff(dd,'17530107',d)/7)*7)+6,'17530107') [weekEnd]
from    AllDatesBetween
order
by      1,2

Answer 3

I'll also share a set of common date functions I use. Just create this as a table valued function:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
    RETURNS @t table (week_start datetime,
                      week_end datetime,
                      lastweek_start datetime,
                      lastweek_end datetime,
                      month_start datetime,
                      month_end datetime,
                      lastmonth_start datetime,
                      lastmonth_end datetime,
                      yesterday_start datetime,
                      yesterday_end datetime,
                      today_start datetime,
                      today_end datetime,
                      thisweek_monday_start datetime,
                      thisweek_monday_end datetime,
                      year_start datetime,
                      year_end datetime,
                      tomorrow_noon datetime,
                      today_noon datetime,
                      date_only datetime)
    BEGIN
       INSERT @t
       SELECT
       dbo.get_week_start ( @date ) AS week_start,
       dbo.get_week_end   ( @date ) AS week_end,
       dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
       dbo.get_week_end   ( DATEADD(d, -7, @date ) ) AS lastweek_end,
       dbo.get_month_start( @date ) AS month_start,
       dbo.get_month_end  ( @date ) AS month_end,
       dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
       dbo.get_month_end  ( DATEADD(m,-1,@date) ) AS lastmonth_end,
       dbo.get_yesterday_start ( @date ) AS yesterday_start,
       dbo.get_yesterday_end ( @date ) AS yesterday_end,
       dbo.get_today_start (@date) AS today_start,
       dbo.get_today_end ( @date ) AS today_end,
       dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
       dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
       dbo.get_year_start(@date) AS year_start,
       dbo.get_year_end(@date) AS year_end,  
       dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
       dbo.get_today_noon(@date) AS TodayNoon,
       dbo.get_date_only(@date) AS DateOnly
    RETURN
    END

Here are the scalar valued functions for these:

   CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
    RETURNS datetime
    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
    AS    
    BEGIN
        RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
    END
    GO

CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
          dateadd(m,1,@date)),0))
END
GO

CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   RETURN dateadd(m,datediff(m,0, @date),0)
   END
GO

CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
GO

CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
GO

CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime 
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(day, 0, datediff(d,0,@today))
END
GO

CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
GO

CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
    + dateadd(ms, -3,
      dateadd(dy, datepart(dy,
     dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
GO

CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
    + dateadd(dy, datepart(dy,
      dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
GO

CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
                                 @date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,@weekday-
      datepart(weekday, @date),@date))-1900, 0)
    + dateadd(ms, -3,
      dateadd(dy, datepart(dy,
      dateadd(weekday,@weekday-datepart(weekday, @date),
                                        @date)),0) )
END
GO

CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
                                   @date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,@weekday-
      datepart(weekday, @date),@date))-1900, 0)
    + dateadd(dy, datepart(dy,
      dateadd(weekday,@weekday-datepart(weekday, @date),
                                        @date))-1,0)
END
GO

CREATE FUNCTION [dbo].[get_year_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
RETURN DATEADD(year, DATEDIFF(year, 0, GetDate())+1, 0)-1
END
GO

CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
GO

CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(ms, -3, datediff(d,0,@today))
END
GO

CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN dateadd(day, -1, datediff(d,0,@today))
END
GO

You could execute this entire table-valued function like so:

SELECT * FROM [MyDB].[dbo].[udfCommonDates] (GetDate())