Finding week start and week end date

Question

I have a week number and year and i need to display "total for mm/dd/yy to mm/dd/yy in a row of my ssrs report. My week starts with Monday. For example if my week number is '2' and year is '2010' then I have to display "total for 01/04/2010 to 01/10/2010 in my ssrs column. how to do this?

Answer 1

SQL Server has a DATEPART function which calculates the ordinal week number of a year. However, you have to call DATEFIRST before this to define which day of the week represents the start of the week. In your case, you have stated that the start of your week is Monday (i.e. 1).

SET DATEFIRST 1;

SELECT SUM([your data column])
FROM [your table]
WHERE DATEPART(WEEKNUM, [your date column])=[your week parameter]
AND DATEPART(YEAR, [your date column])=[your year parameter]

Answer 2

Try this

declare @year char(4) = '2010'
declare @week int = 2
declare @fromdate datetime
declare @todate datetime
set @fromdate = DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @year) + (@week-1), 7);
set @todate = DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @year) + (@week-1), 6) ;
;WITH dates AS 
(
    SELECT CONVERT(datetime,@fromDate) as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @toDate
)
select * from dates

Answer 3

Your description is not american standard nor isoweek. Seems like a mix of those. I never heard of that as a standard. It is nearly isoweek. So that is what this answer is based on.

Calculating iso year is a bit tricky, you can read about it here:

This is the syntax you need:

DECLARE @year int = 2010
DECLARE @week int = 2
;WITH CTE AS
(
  SELECT 
      dateadd(wk,  datediff(wk, - @week * 7, 
        cast(cast(@year as char(4)) as datetime) - 5), 0) startofweek
)
SELECT 
  replace('total for ' + convert(char(10), startofweek, 110)
  + ' to ' + convert(char(10), dateadd(day, 6, startofweek) , 110), '-', '/')
FROM CTE

Result:

total for 01/11/2010 to 01/17/2010

Isoweek 2 in 2010 is 2010-01-11

Answer 4

Try setting DATEFIRST (https://msdn.microsoft.com/en-ie/library/ms181598.aspx)

SET DATEFIRST 7

declare @wk int  
declare @yr int
declare @EndOfWeek as datetime

set @wk = 2
set @yr = 2010


SET @EndOfWeek = dateadd (week, @wk, dateadd (year, @yr-1900, 0)) + 1 - datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) )

SELECT 
    replace('total for ' + convert(char(10), dateadd(day, -6, @EndOfWeek) , 110)
  + ' to ' + convert(char(10), @EndOfWeek, 110), '-', '/')

The result:

total for 01/04/2010 to 01/10/2010

Answer 5

I hope it helps:

declare @year char(4) = '2014'
declare @week int = 2
select dateadd(week,@week,convert(date,@year+'-01-01',121))

Change the date format appropriate for you from this list