question

billj avatar image
billj asked

Calculate end date for each week

Hello, I would like to calculate the end date for each week for a specific year. My problem is can't figure it out how to get the end date for each week. Manually I use the following query that shows the output. I use year 2015 for this sample. How to calculate '01/10/2015', '01/17/2015', .... '12/12/2015' using a formula. SELECT DATEDIFF (ww, '01/01/2015', '01/10/2015') -- Week 1 SELECT DATEDIFF (ww, '01/01/2015', '01/17/2015') -- Week 2 SELECT DATEDIFF (ww, '01/01/2015', '01/24/2015') -- Week 3 SELECT DATEDIFF (ww, '01/01/2015', '01/31/2015') -- Week 4 SELECT DATEDIFF (ww, '01/01/2015', '02/07/2015') -- Week 5 SELECT DATEDIFF (ww, '01/01/2015', '02/14/2015') -- Week 6 SELECT DATEDIFF (ww, '01/01/2015', '02/21/2015') -- Week 7 SELECT DATEDIFF (ww, '01/01/2015', '02/28/2015') -- Week 8 ................................................. SELECT DATEDIFF (ww, '01/01/2015', '12/12/2015') -- Week 49 (current week) SELECT DATEDIFF (ww, '01/01/2015', '12/19/2015') SELECT DATEDIFF (ww, '01/01/2015', '12/26/2015') SELECT DATEDIFF (ww, '01/01/2015', '01/02/2016') -- Week 52 Please help me to solve my problem.
sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
It looks like you don't want to include the partial week at the beginning of the year as a separate week, so that's the way I handled it. This should do what you want for any given year. DECLARE @year int = 2015; DECLARE @startDate date = DATEADD(year, @year - 1, CONVERT(date, '01/01/0001')); DECLARE @week1 date; IF DATEPART(weekday, @startDate) = 7 SET @week1 = @startDate; ELSE SET @week1 = DATEADD(d, 14 - DATEPART(weekday, @startDate), @startDate); -- partial + full week SELECT @week1; WITH WeekCounter AS ( SELECT TOP(52) WeekNumber = ROW_NUMBER() OVER (ORDER BY c1.object_id) FROM sys.columns c1 CROSS JOIN sys.columns c2 ) SELECT wc.WeekNumber, DATEADD(week, wc.WeekNumber - 1, @week1) FROM WeekCounter wc ORDER BY wc.WeekNumber ;
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

billj avatar image billj commented ·
Tom, Thanks a lot for your help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.