How to get the week start and end dates?

Question

I have variable called WeekBeginDate and I want only to pull data for that week. For example, if the beginning of the week date is 07/21/2014 which is Monday in this case, then I want only to pull the data from 07/21/2014 to 7/27/2014.

The variable will always contain the date for the beginning of the week only but I don’t have the date for the end of the week.

The week begins on Monday and ends on Sunday. I can’t figure out how to calculate or sum the number of hours if I only have the date for the beginning of week.

SELECT DT, sum (TOT_HOURS)as TOT_HOURS FROM MYTABLE where DT >= @WeekBeginDate and <=@WeekEndDate group by DT

Note, that I only have the variable for the WeekBeginDate.

possible duplicate of [Getting the Starting and ending date of week? In Sql server?](http://stackoverflow.com/questions/10007861/getting-the-starting-and-ending-date-of-week-in-sql-server) — DavidG, Jul 30 '14 at 14:08

score 1 · Answer 1 · answered Jul 30 '14 at 14:15

just modify your table columns in this CTE it may works :

;WITH workhours AS
(
    SELECT  DATEADD(DAY
                ,   -(DATEPART(dw, DT) -1)
                ,   DT) AS week_start
        ,   DATEADD(DAY
                ,   7 - (DATEPART(dw, DT))
                ,   DT) AS week_end

    FROM    MYTABLE
)
SELECT      week_start
        ,   week_end
        ,   SUM(TOT_HOURS) total_hrs_per_week
FROM        workhours
GROUP BY    week_start
        ,   week_end

score 1 · Answer 2 · answered Jul 30 '14 at 14:50

You may need to add 6 days to the beginning of the week and group by something else if you need total weekly hours, i'm calling it "id". not by dt (or don't group at all if it is a total for the whole table):

SELECT id, DT, sum (TOT_HOURS)as TOT_HOURS FROM MYTABLE 
where DT BETWEEN @WeekBeginDate and DATEADD(d,6,@WeekBeginDate)
GROUP BY id

score 1 · Answer 3 · answered Jul 30 '14 at 18:56

This should be of some use to you. I am casting to date so the 24 hrs of day is considered.

DECLARE @WeekBeginDate DATETIME

SET @WeekBeginDate = '2014-07-28 12:08:31.633';
WITH MYTABLE (DT,TOT_HOURS)
    AS (
    SELECT '2014-06-27 00:08:31.633',5 UNION ALL
    SELECT '2014-07-27 00:08:31.633',5 UNION ALL
    SELECT '2014-07-28 00:08:31.633',1 UNION ALL
    SELECT '2014-07-29 00:08:31.633',1 UNION ALL
    SELECT '2014-07-30 00:08:31.633',1 UNION ALL
    SELECT '2014-07-31 00:08:31.633',1 UNION ALL
    SELECT '2014-08-01 00:08:31.633',1 UNION ALL
    SELECT '2014-08-02 00:08:31.633',1 UNION ALL
    SELECT '2014-08-03 00:08:31.633',1
    )
SELECT  CAST(@WeekBeginDate AS DATE) AS StartDate,
       DATEADD(d, 6, CAST(@WeekBeginDate AS DATE)) AS EndDate,
       SUM (TOT_HOURS)AS TOT_HOURS
FROM MYTABLE
WHERE CAST(DT AS DATE) BETWEEN CAST(@WeekBeginDate AS DATE) AND DATEADD(d, 6, CAST(@WeekBeginDate AS DATE))

score 0 · Answer 4 · answered Jul 30 '14 at 14:10

0

Just add 6 (or 7) days...

SELECT DT, sum (TOT_HOURS)as TOT_HOURS FROM MYTABLE 
where DT BETWEEN @WeekBeginDate and @WeekBeginDate + 6 group by DT

answered Jul 30 '14 at 14:10

T McKeown

12,971
1
25
32

score 0 · Answer 5 · answered Jul 30 '14 at 14:57

select @weekBeginDate = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

select @WeekEndDate = DATEADD(dd, 6, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

SELECT DT, sum (TOT_HOURS)as TOT_HOURS FROM MYTABLE where DT >= @WeekBeginDate and <=@WeekEndDate group by DT

score 0 · Answer 6 · answered Jul 30 '14 at 19:08

Here is where having a calendar table would be very useful, especially if your logic needs to change if Monday is a holiday.

Basically create a table with pre-calculated values for weeks and just join to it.

http://www.made2mentor.com/2011/04/calendar-tables-why-you-need-one

How to get the week start and end dates?

6 Answers6