Week number and its Sunday

Question

I have a simple question.

If I have a week number, how I assigned a Sunday to it? (In t-sql)

For example. I have number 15 as input (which is week from 8.4. to 14.4., from monday to sunday), and I will need it to be shown as 14.4. as output. The things is, that I have a column of days converted to number of week (col1), and I need a Sunday of this week number (col2).

col1    col2
15      14.4.
15      14.4.
15      14.4.
15      14.4.
16      21.4.
16      21.4.
17      28.4.
17      28.4.
19      12.5.
19      12.5.

What do you mean by week 8.4? What is.4 of a week? If Week 15 is Week 8.4, when does the year start; it implies 7 weeks and a bit into the year, which suggests week 1 would be just before the end of February? — Thom A, Nov 04 '19 at 11:19
But the 8th of April could be any day of the week, @tymtam . So a week can't always start of that day (08 April), unless the OP's starting day of the week changes every year? That would seem very odd. For example 08 April was a Monday in 2019, but it's a Wednesday in 2020. Does that mean in 2020 the OP's starting day of the week is a Wednesday? — Thom A, Nov 04 '19 at 11:24

score 1 · Answer 1 · answered Nov 04 '19 at 11:23

Weeks start on different days in sql server based on regional settings.

What i have done, i have created a table with week number and year and what date it is. Then you can fill the table relevante date ranges. In this way you can run a query like

Select startDate from weeks where year=2018 and weekNumber=42

Peter Ksenak · Accepted Answer · 2019-11-04T11:52:29.310

1

Use this, remember, you need know also the year, not only weekNo

Week in this case start on Monday and end on Sunday, you will get the sunday in requested week

 DECLARE @WeekNum int
 DECLARE @YearNum char(4)

 set @WeekNum = 46
 set @YearNum = 2019

  SELECT @WeekNum As WeekNo,  FORMAT(CONVERT(date,DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 6)), 'dd.MM.yyyy') AS SundayInWeek

edited Nov 04 '19 at 11:52

answered Nov 04 '19 at 11:42

Peter Ksenak

305
2
5

Thanks. That works in kind of my way. I am quite new to SQL, is there any way how "set@WeekNum" can be read from column´´s table? In another words, how to use it for each column of table – Jan Janoušek Nov 04 '19 at 12:20
UPDATE yourTable SET col2 = FORMAT(CONVERT(date,DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (col1-1), 6)), 'dd.MM.yyyy') - but do not forget, you do not have year in your table, so create another column in your table called for ex. Year and replace @YearNum to your new created column in update clause – Peter Ksenak Nov 04 '19 at 12:38

tymtam · Answer 3 · 2019-11-04T12:13:59.270

select FORMAT( DATEADD(week, @weekNo, DATEADD(WEEK, DATEDIFF(WEEK, '19050101', DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)), '19050101')), 'd.M');

14.4

The start of week that's always Sunday is by Aaron Bertrand at Get first day of week in SQL Server

Here's the train of 'thought'

DECLARE @start_of_year date = (select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0));
--by Aaron Bertrand at https://stackoverflow.com/questions/7168874
DECLARE @sunday_of_the_first_week date = (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @start_of_year), '19050101'));
DECLARE @sunday_of_week15 date = (select DATEADD(week, 15-1 , @sunday_of_the_first_week));
DECLARE @sunday_after_week15 date = (select DATEADD(week, 1 , @sunday_of_week15));


select @start_of_year, @sunday_of_the_first_week, @sunday_of_week15, @sunday_after_week15, FORMAT(@sunday_after_week15, 'd.M');

2019-01-01  2018-12-30  2019-04-07  2019-04-14  14.4

Please note that if you have the date it could be:

-- get first Sunday after a date
declare @d date = GetDate();

SELECT FORMAT(DATEADD(WEEK, 1+DATEDIFF(WEEK, '19050101', @d), '19050101'), 'd.M');

10.11

Week number and its Sunday

3 Answers3