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

Question

Post reply

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

Frank Peters

SSC Veteran

Points: 211
More actions
August 5, 2005 at 4:20 am

#102686

How can I determine the startdate and enddate of a given ISOweek number and a year??
Can someone help me!!!

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

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

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

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)

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

ISO 8601

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

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

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)

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 7

Nice! Glad that you liked the article.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

bobby 17736 Newbie Points: 1 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 9

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)