Displaying start date of week in SQL

Question

I have a SQL query that to return the number of items per week. I have a query that returns so far this:

Number of Items  |  Week Number
-------------------------------
        100      |     18
        80       |     19
        120      |     20

And would like to return the following:

Number of Items  |  Week Beginning
-------------------------------
        100      |     1st May 2017
        80       |     8th May 2017
        120      |     15th May 2017

What I have so far is:

SELECT COUNT(*) AS 'Number of Items', DATEPART(WEEK, Date) FROM table
where DATEPART(Year, Date) = '2017' and DATEPART(MONTH, Date) = 5
group by DATEPART(WEEK, Date)

Possibly a duplicate of https://stackoverflow.com/questions/607817/get-dates-from-a-week-number-in-t-sql — Jonny, Jul 13 '17 at 12:53
Can you share a snippet or sample of the data from where you need the above table? Would be helpful. — samkart, Jul 13 '17 at 12:53

score 0 · Answer 1 · answered Jul 13 '17 at 12:55

You are talking about the 1st day of the current week:

example:    select FORMAT(dateadd(ww,datediff(ww,0,getdate()),0),'dd MMM yyyy')--if you are using SQL 2012+

answer:  
SELECT COUNT(*) AS 'Number of Items', FORMAT(dateadd(ww,datediff(ww,0,date_column),0),'dd MMM yyyy')
FROM table
where DATEPART(Year, Date) = '2017' and DATEPART(MONTH, Date) = 5
group by DATEPART(WEEK, Date)

score 0 · Answer 2 · answered Jul 13 '17 at 12:59

As you need Monday to be the first day of the week

select  DATEPART(WEEK, MyDate),DATEADD(DAY,1,(DATEADD(DAY, 1-DATEPART(WEEKDAY, MyDate), MyDate)))
    from (
    select '5/3/2017' MyDate
    union all select '5/10/2017'
    union all select '5/14/2017')A

score 0 · Answer 3 · answered Jul 13 '17 at 13:11

0

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Date) /7*7, 0) AS StartDateOfWeek

answered Jul 13 '17 at 13:11

score 0 · Answer 4 · answered Jul 13 '17 at 13:13

check this if it solves

DECLARE @WeekNum INT
      , @YearNum char(4);

SELECT @WeekNum = 20 
     , @YearNum = 2017
-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

thanks to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185440

Displaying start date of week in SQL

4 Answers4