# Determine Start/End date of given (ISO)week and year

• How can I determine the startdate and enddate of a given ISOweek number and a year??

Can someone help me!!!

• This should work I think:

`DECLARE @year INT, @isoweek INT`

`SET @year = 2005`

`SET @isoweek = 31`

`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`

• Frank,

I'm not familiar with the rules for ISO Weeks... have heard that the first week of a year must have 4 or more days to qualify as week one or some such... what are the rules concerning ISO weeks?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• ISO 8601

There is a short description of it in my article: On the Trail of the ISO Week.

• Thanks Chris,

I'll take a look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Nice article, Chris... excellent links, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Frank and Chris,

Because the 1st of January, 1900 was a Monday, this also works regardless of the setting of DateFirst.

DECLARE @Year    CHAR(4)

DECLARE @ISOWeek VARCHAR(2)

SET @Year    = '2005'

SET @ISOWeek = '31'

SELECT DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7) AS StartDate,

DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate

Chris... special thanks to you for the great article that led me to this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Nice! Glad that you liked the article.

• Big thanks to Jeff and Chris on this one - I was having a head-scratching moment as I was trying to roll calculating the start of a given week (from ISOWeekNo and Year) into some existing reports and I could not think of a clean and simple method for the calculation (i.e. avoiding UDFs, date lookup tables etc) and also avoiding complexity around @@datefirst.

Just wanted to express my gratitude as this has ended a good 2 hours of scratching ideas on my notepad.

• Thanks for the feedback, especially after a whopping 16 years! 😀

While the code I posted still works, I've developed a real hate for the "wk" date-part.  I also normally don't go for "End Dates" because of the mantra of always using SomeDate >= SomeStartDate and SomeDate < SomeEndDate+1 where SomeEndDate+1 is called a "CutOff Date" in an Closed/Open date range.

With that, here's what I use now (except I use different column names than this) and have included the end date just for backward compatibility.  I also got rid of all character based conversions.

`DECLARE  @Year    INT       = 2005        ,@ISOWeek TINYINT   = 31; SELECT  v.StartDate        ,EndDate    = DATEADD(dd,6,v.StartDate) --I normally don't include this but did for completness.        ,CutoffDate = DATEADD(dd,7,v.StartDate) --First day of the Next ISO Week.   FROM (VALUES(DATEADD(dd,DATEDIFF(dd,0,DATEFROMPARTS(@Year,1,4))/7*7,(@ISOWeek-1)*7)))v(StartDate);GO`

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply