Grouping by Week in SQL, but displaying full datetime?

Question

the following statement:

group by datepart(wk, createdon)

Groups the selected rows according to the week in which they fall. My select statement shows the following:

SELECT 
    datepart(wk, createdon) week,

How do I display the actual datetime of the week("12-10-2012"), instead of the number ("12")?

Do you want to display the actual value of createdon for each row? If you do, then you can't "group" the rows according to the week. Do you want to display some specific date for any given week, like the date of the corresponding Monday, or some such? If so, we need to know which one. — João Mendes, Nov 07 '12 at 23:24
What do you want to display if you have multiple dates in the same week? — Taryn, Nov 07 '12 at 23:26
I'd like to display the actual date of the beginning of the week - like "date of" + createdon. — RobVious, Nov 07 '12 at 23:35
possible duplicate of [Get first day of week in SQL Server](http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) — Andrew Marshall, Nov 08 '12 at 04:55

score 2 · Answer 1 · answered Nov 07 '12 at 23:17

2

One option is using a cte with ROW_NUMBER instead:

WITH CTE AS 
(
   SELECT 
       RN = ROW_NUMBER() OVER (PARTITION BY datepart(wk, createdon) ORDER BY createdon)
     , DATEADD(ww, DATEDIFF(ww,0,createdon), 0) As Week
     , *
   FROM dbo.Table
)
SELECT * FROM CTE
WHERE RN = 1

answered Nov 07 '12 at 23:17

Tim Schmelter

450,073
74
686
939

Everyone: don't overlook this answer. It offers up an elegant way of doing what we formerly did with temporary tables in the past. +1 – Michael Blankenship Jul 30 '15 at 18:01

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

If I understand you correctly, you're wanting to group by week but also return the full datetime. You must include the selected fields in aggregate columns. Thusly should work:

SELECT DATEPART(wk, createdon) week, createdon
FROM TableName
GROUP BY DATEPART(wk, createdon), createdon

Works on MSSQL2008R2

Edit: As the OP seems to want the starting date of the week, SO has the answer.

edited May 23 '17 at 12:06

Community

1
1

answered Nov 07 '12 at 23:23

jbrinkley

46
4

This is a well-written answer, but it doesn't appear to address the original poster's needs, as this approach doesn't group rows together that are in the same week. (Unless I've missed something?) – Jamey Sharp Nov 07 '12 at 23:44
LOL, as I said, *if* I understand...which it seems now the OP wants to display the "date" of the starting "day" of the week. – jbrinkley Nov 08 '12 at 00:03
Therefore, I think SO already has the [answer](http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server). – jbrinkley Nov 08 '12 at 00:12
Good catch! Looks plausible to me. You might update your answer saying so. – Jamey Sharp Nov 08 '12 at 00:17

score 1 · Answer 3 · edited Nov 16 '12 at 22:06

1

You can't group by a week, and then show constituent parts of it, i.e. days. You will need group by the date instead.

edited Nov 16 '12 at 22:06

Flexo

87,323
22
191
272

answered Nov 07 '12 at 23:17

user1731782

322
2
12

Grouping by Week in SQL, but displaying full datetime?

3 Answers3