Get first and last date from week and year

Question

I am trying to retrieve the first and last date from a week and year in T-SQL. I found some exemples in stackoverflow or in google but nothing work well. The better exemple I find is this one :

DECLARE @WeekNo int= 20
DECLARE @Year int=2019

SELECT DATEADD(wk,@WeekNo-1,DATEADD(yy,@Year-1900,0)) AS WeekStart,
DATEADD(wk,@WeekNo,DATEADD(yy,@Year-1900,0))-1 AS WeekEnd

But I need the week start on a monday. And if I change it, it works for 2019 and not 2020. I am really stuck on this point.

Edit : First day of the week need to be monday. And week number are iso formated. First week of the year start only if 1 january is before a Friday (4 days in nexw year needed to be first week)

Please have a look into this https://www.codeproject.com/Tips/5161640/SQL-Server-Find-Week-Start-And-End-DateTime — Suraj Kumar, Mar 26 '20 at 08:27
Tanks, but this function use a complete date and not juste a year and week number — Etienne, Mar 26 '20 at 08:31
The year 202 started on a Wednesday. If you're looking for week number 1 in 2020 - do you want to get 2019-12-30 and 2020-01-05 as your dates for start and end of this week, or perhaps week one is the first whole week in the year (from Jan 6th to 11th)? Maybe you want Jan 1st and 5th as the dates, even though the week starts two days earlier - This question can't be answered before you clarify these ground rules. — Zohar Peled, Mar 26 '20 at 09:37
I agree with @ZoharPeled, you must be very specific with your requirements. Having said that, the first day of the week is based on your server's language config which means if the default (us_english) is used SELECT @@DATEFIRST will return 7 (Sunday). So if you begin your query with SET DATEFIRST 1; Monday will be used as first day. — Thailo, Mar 26 '20 at 10:33
To get the date of Monday equal or prior to a given date you can use [this](https://stackoverflow.com/questions/60321796/get-first-day-of-week-t-sql/60323404#60323404) answer. The result is independent of the setting of `DateFirst` and `Language`. — HABO, Mar 26 '20 at 17:52

score 1 · Accepted Answer · answered Mar 26 '20 at 09:49

You can try this below logic to find start and end date of a week from week and year number. This will consider Monday as a week starter.

(Logic taken from online search)

DECLARE @year INT, @isoweek INT

SET @year = 2020
SET @isoweek = 14

SELECT
DATEADD(
    d, 
    (@isoweek - 1) * 7,
    CASE (DATEPART(dw, CAST(@year AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
        WHEN 1 THEN CAST(@year AS CHAR(4)) + '-01-04'
        WHEN 2 THEN DATEADD(d, -1, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 3 THEN DATEADD(d, -2, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 4 THEN DATEADD(d, -3, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 5 THEN DATEADD(d, -4, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 6 THEN DATEADD(d, -5, CAST(@year AS CHAR(4)) + '-01-04')
        ELSE DATEADD(d, -6, CAST(@year AS CHAR(4)) + '-01-04')
    END

) AS startdate , 

DATEADD(
    d, 
    (@isoweek) * 7 - 1,
    CASE (DATEPART(dw, CAST(@year AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
        WHEN 1 THEN CAST(@year AS CHAR(4)) + '-01-04'
        WHEN 2 THEN DATEADD(d, -1, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 3 THEN DATEADD(d, -2, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 4 THEN DATEADD(d, -3, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 5 THEN DATEADD(d, -4, CAST(@year AS CHAR(4)) + '-01-04')
        WHEN 6 THEN DATEADD(d, -5, CAST(@year AS CHAR(4)) + '-01-04')
        ELSE DATEADD(d, -6, CAST(@year AS CHAR(4)) + '-01-04')
    END
) AS enddate

score 0 · Answer 2 · answered Mar 26 '20 at 09:04

I would suggest using a reference monday (in the past), which is your base in order to find the monday in your week. Following an example:

DECLARE @WeekNo int= 20
DECLARE @Year int=2020
DECLARE @RefMonday date = CAST('1990-01-01' AS DATE)

DECLARE @d date = DATEADD(d, 7*@WeekNo, CAST(CAST(@Year AS VARCHAR(4))+'-01-01' AS DATE))
DECLARE @offset int = DATEDIFF(d, @RefMonday, @d)%7

DECLARE @StartOfWeek date = (SELECT DATEADD(DD, -1 * @offset, @d))
DECLARE @EndOfWeek date = DATEADD(d, 6, @StartOfWeek)

SELECT @StartOfWeek AS StartOfWeek, @EndOfWeek AS EndOfWeek

Get first and last date from week and year

2 Answers2