Converting a PeriodStart and PeriodEnd to year + week number

Question

I'm trying to convert a start + end date into multiple rows filled with weeknumbers and the year connected to it. My idea is to convert this start + end date in SSIS with a Script component, and create rows for each week by setting SynchronousInput to 'None'. I tried using a Timespan for this however this does not count through to the next year when reaching above week 53.

Does anyone know how to accomplish this?

Example:

Input:

StartDate   EndDate
2014-12-29  2015-02-01

Wanted output:

  Year  WeekNr
  2015      1
  2015      2
  2015      3
  2015      4
  2015      5

There are 21 days between 5 and 26. How do you get 5 weeks? Is it full weeks starting on Monday or on StartDate? — Julien Vavasseur, Nov 05 '15 at 12:53
corrected it but the point is to take the week number of the date (26 january is in week 5) — Alim, Nov 05 '15 at 12:58
2015-01-05 will never be week 1, not in ISO format, not in US format. Maybe you should describe exactly how to convert to your wanted output — t-clausen.dk, Nov 05 '15 at 13:03
well it actually is week 1 on the dutch calendar (http://www.timeanddate.com/calendar/?year=2015&country=25&save=Show+%26+Save) — Alim, Nov 05 '15 at 13:05
no, just checked, 2015-01-05 is week 2 in the dutch calendar. According to this [link](http://www.timeanddate.com/calendar/custom.html%3Fcountry%3D25%26lang%3Den%26lang2%3Dnl%26hol%3D9%26wno%3D1), it seems you are using the iso calendar — t-clausen.dk, Nov 05 '15 at 13:10
I would construct a calendar table - pre-populate that one time + yearly maintenance job on it as required then join to that based on start / end and pull out the week numbers you pre-calculated. This also allows you to deal with differences in how companies view the calendar (52 week vs 53 week). If you need contiguous numbers you can then use ranking functions. — Andrew, Nov 05 '15 at 13:31

score 1 · Accepted Answer · edited May 23 '17 at 11:58

At first, I used the formular from here, but then remembered that, there is a better way using sqlserver 2008+ and changed the script to be more readable:

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT
  datepart(iso_week, dateadd(week, n-1, @from)) weeknr,
  datepart(year, dateadd(week, datediff(day, -7*n, @from)/7, -4)) Year
FROM TALLY
WHERE
  datediff(ww, dateadd(d, -1, @from), dateadd(d, -1, @to))+1 >= n

Result:

weeknr  Year
1       2015
2       2015
3       2015
4       2015
5       2015

Converting a PeriodStart and PeriodEnd to year + week number

1 Answers1