T-SQL display week ending dat when using DatePart(ww, Date)

Question

I have a query where I am grouping by week using DatePart(ww , [TimeEntryDate]) for the column and it returns the week number. I would like to display the week ending date and like that to always be a saturday.

Here's the complete query

SELECT 
DatePart(year , [TimeEntryDate]) [YEAR],
DatePart(ww , [TimeEntryDate]) Week, 
CategoryId, 
SUM(TimeEntryDuration) "Total Hours"

  FROM [TIMETRACKER].[dbo].[aspnet_starterkits_TimeEntry]
  GROUP BY DatePart(year , [TimeEntryDate]),  DatePart(ww , [TimeEntryDate]) , CategoryId 
  ORDER BY 1,2,3

data 
Year    Week   Category  Total Hours
2010     1        1          10
2010     2         1         12

Leons · Accepted Answer · 2011-07-29T12:10:39.367

2

You can try the following

SELECT DatePart(year , [TimeEntryDate]) [YEAR],
       DatePart(ww , [TimeEntryDate]) Week, 
       DATEADD(d, 7 - DATEPART(DW, [TimeEntryDate]), [TimeEntryDate]) as WeekEnding,
       CategoryId, 
       SUM(TimeEntryDuration) "Total Hours"  
  FROM [TIMETRACKER].[dbo].[aspnet_starterkits_TimeEntry]  
 GROUP BY DatePart(year , [TimeEntryDate]),  DatePart(ww , [TimeEntryDate]) , CategoryId 
 ORDER BY 1,2,3

EDIT: The settings for start date is based on your language settings. The U.S. English default is 7, Sunday, but you need to verify your settings and adjust the calculation accordingly. You can verify your settings to determine which day is the start of the week with

SELECT @@DATEFIRST;

This query will illustrate the difference:

SET LANGUAGE Italian;
GO
SELECT @@DATEFIRST;
GO
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;

You can also set your week start date as follows.

SET DATEFIRST 1

edited Jul 29 '11 at 12:10

answered Jul 28 '11 at 19:57

Leons

2,679
1
21
25

I may be wrong, but if the OP says that Saturday is the end of the week, then obviously Sunday is its beginning, and therefore it is safer to assume that `DATEPART` returns 1 for Sunday, rather than 7. Consequently, your expression for `WeekEnding` should contain `6 - DATEPART…`, not `7 - …`. Otherwise I absolutely agree with the approach. – Andriy M Jul 28 '11 at 21:08
@Andriy-m It appears that the default settings for SQL Server is 1 for Monday and 7 for Sunday, so the expression is still valid. It does produce the right date in a SQL statement and that is good for me. – Leons Jul 29 '11 at 02:15
I would be surprised if it was the case on the majority of systems in US, where weeks start on Sundays. This is what localisation is for. – Andriy M Jul 29 '11 at 03:13
@Andriy-m You are correct, it is all about localization. I will edit my post to indicate. – Leons Jul 29 '11 at 12:05

score 0 · Answer 2 · edited May 23 '17 at 11:47

This will give you the Saturday date for any week: (courtesy of this post from @mwigdahl)
**note: you don't need the CONVERT() function, but it makes it more readable*

CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, @date), @date)), 101)

Here's a test to demonstrate it:

DECLARE @date smalldatetime
SET @date = '7/1/11'

DECLARE @week int
SET @week = datepart(ww, @date)

WHILE @week <= 32 --just to take the test through July
BEGIN
   SELECT 
        @week, 
        @date,
        CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, @date), @date)), 101)
    SET @date = @date + 1
    SET @week = datepart(ww, @date)
END

T-SQL display week ending dat when using DatePart(ww, Date)

2 Answers2