SQL Server Get the first date of the week, given the week number?

Question

I have a query (for use in bug tracker.net) that calculates the number of errors by week by status. But the query returns the week number, I really want this is the first date of the week.

select datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week], bg_status , st_name as [status], count(*) as [count] from bugs inner join statuses on bg_status = st_id group by datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))), bg_status, st_name order by [week], bg_status 

The part that gets the week number,

 datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week] 

It returns this output:

 week bg_status status count ----------- ----------- --------------------------------------------- ------ 22 1 new 1 22 5 closed 32 

But it would be better to say the first date of each week, for example 01-01-2010, then 08-01-2010, etc.

The question is not a duplicate. How do you get the "start date of the week" and the "end date of the week" from the week number in SQL Server? (the answer says how to start a week from a date, not a week number)

Not a duplicate. Calculate date from week number (question asks C #)

Not a duplicate Get the first date of the week from the provided date (javascript asks the question)

I searched, but could not find the answer to this question for SQL Server (if that matters in 2010)

Answer 1

If you think about it correctly, the answer to SO 1267126 can be applied to your problem.

Each of the marked date errors that you have in the group is displayed for the same week. Therefore, by definition, each of these error dates should also appear at the same start of the week. Thus, you start the calculation of the “beginning of the week from a given date” in the dates of the error report, and also calculate the number of weeks and group both (modestly terrible) expressions and finally get the answer you are looking for.

 SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week], DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date) AS [weekstart], bg_status, st_name AS [status], COUNT(*) AS [count] FROM bugs INNER JOIN statuses ON bg_status = st_id GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))), DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date), bg_status, st_name ORDER BY [week], bg_status 

---

Since bg_reported_date is DATETIME (see the comment, it includes a time component), you must specify it in DATE before defining the start of the week (but the expression of the week number is not required, and the "week of the week" at the beginning of the beginning of the week expression is not needed):

 SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week], DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), CAST(bg_reported_date AS DATE)) AS [weekstart], bg_status, st_name AS [status], COUNT(*) AS [count] FROM bugs INNER JOIN statuses ON bg_status = st_id GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))), DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), CAST(bg_reported_date AS DATE), bg_status, st_name ORDER BY [week], bg_status 

NB: unconfirmed code!

Answer 2

I understand that this is a very old thread, but "Get the first date of the week, given the week number" is exactly what I wanted to do, and I do not have an actual date for work, so the accepted answer does not work for me. I thought I would forward my solution to posterity. Please note that I suspect that different culture settings MAY break this, so use a test before use.

My answer is built starting from this .

Suppose you know the week number and year, and you want to get the start and end dates of this week of the same year. Here is what I have:

 --These 2 "declared" variables would be passed in somehow declare @WeekNumber int = DATEPART(wk, GETDATE()) declare @ForYear int = YEAR(GETDATE())-1 --Since we don't have a raw date to work with, I figured I could just start with --Jan 1 of that year. I'll store that date in a cte here, but if you are doing this --in a stored proc or function, it would make much more sense to use another @variable ;with x as ( --this method works in SQL 2008: SELECT CONVERT(DateTime, ('1/1/' + CONVERT(varchar, @ForYear))) as Jan1ForSelectedYear --If you are using 2014 or higher, you can use this instead: --DATETIME2FROMPARTS(@ForYear, 1, 1, 0,0,0,0,0) ) --Now that we have a date to work with, we'll just add the number of weeks to that date --That will bring us to the right week number of the given year. --Once we have THAT date, we can get the beginning and ending of that week --Sorry to make you scroll, but I think this is easier to see what is going on this way SELECT CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear) - 6, x.Jan1ForSelectedYear))), 101) as FirstDayOfWeekXForSelectedYear, CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear) , x.Jan1ForSelectedYear))), 101) as LastDayOfWeekXForSelectedYear FROM x