August 5, 2005 at 4:20 am
How can I determine the startdate and enddate of a given ISOweek number and a year??
Can someone help me!!!
August 5, 2005 at 5:31 am
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
August 7, 2005 at 4:11 pm
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
August 8, 2005 at 12:49 am
There is a short description of it in my article: On the Trail of the ISO Week.
August 8, 2005 at 4:54 am
Thanks Chris,
I'll take a look.
--Jeff Moden
August 8, 2005 at 5:08 am
Nice article, Chris... excellent links, as well.
--Jeff Moden
August 8, 2005 at 6:21 am
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
August 8, 2005 at 7:38 am
Nice! Glad that you liked the article.
December 15, 2021 at 2:09 pm
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.
December 15, 2021 at 10:58 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply