How to get the Starting date and ending date of weeks using week number in my case using SQL Server?

Question

I already find to get the week number but, that shows the date in different way.

Actually When I check the week number for the Date of

select datepart(wk,'2016-01-02')  //Saturday

output: 1

select datepart(wk,'2016-01-03')  //Sunday

output: 2

But when I get the date of starting and ending date of weeks using week number, It shows different.

DECLARE @weekStart INT
DECLARE @weekEnd INT
DECLARE @Year INT

set @weekStart = 1
set @Year = 2016

DECLARE @WeekStartDate date
DECLARE @WeekEndDate date

SET @WeekStartDate = convert(date,DATEADD (WEEK, @weekStart, DATEADD (YEAR, @Year-1900, 0)) - 4 -
                                                DATEPART(DW, DATEADD (WEEK, @weekStart, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1)
SET @WeekEndDate =convert(date,DATEADD (WEEK, @weekStart+1, DATEADD (YEAR, @Year-1900, 0)) - 4 -
                                                DATEPART(DW, DATEADD (WEEK, @weekStart+1, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1)

select @WeekStartDate,@WeekEndDate


output: 
  StartingDate    EndingDate
  --------------------------
  2016-01-03      2016-01-09

I am expecting output is, if I give week = 1 it should give StartingDate = **2016-01-01 and **EndingDate =2016-01-02

for the week number 2, it should give 2016-01-03 2016-01-09

Answer 1

You can get the absolute start/end for the week and then adjust for the current year the first week start and the last week end. You only really need to calculate the start because the end is start + 6 days:

create procedure spWeekDates @year int, @week int
as
declare @firstWeekDay int
declare @yearStart datetime, @weekStartDate datetime, @weekEndDate datetime

set datefirst 7 -- change as needed
set @yearStart=cast(@year as char(4))+'0101' -- years always start on 01/01 [citation needed]
set @firstWeekDay=datepart(weekday,@yearStart)

-- absolute start/end dates
set @weekStartDate=dateadd(week,@week-1,@yearStart)-@firstWeekDay+1
set @weekEndDate=dateadd(day,6,@weekStartDate)
-- adjusting for target year
if year(@weekStartDate)<@year set @weekStartDate=@yearStart
if year(@weekEndDate)>@year set @weekEndDate=cast(@year as char(4))+'1231'

select @weekStartDate as WeekStartDate, @weekEndDate as WeekEndDate
go

exec spWeekDates 2016,1
exec spWeekDates 2016,2
exec spWeekDates 2016,53
go

Results:

|       WeekStartDate |         WeekEndDate |
|---------------------|---------------------|
| 2016-01-01 00:00:00 | 2016-01-02 00:00:00 |

|       WeekStartDate |         WeekEndDate |
|---------------------|---------------------|
| 2016-01-03 00:00:00 | 2016-01-09 00:00:00 |

|       WeekStartDate |         WeekEndDate |
|---------------------|---------------------|
| 2016-12-25 00:00:00 | 2016-12-31 00:00:00 |

Answer 2

You have to get day of the week of first day in year
if it doesn't equals 1 then you have to set @weekStart = @weekStart -1
and set @StartingDate = first day in year