SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 ISO Week of Year Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/18/2006 :  19:36:52  Show Profile  Reply with Quote
This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.

This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.



drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
	(
	@Date	datetime
	)
returns		int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear		int

select
	-- Compute week of year as (days since start of year/7)+1
	-- Division by 7 gives whole weeks since start of year.
	-- Adding 1 starts week number at 1, instead of zero.
	@WeekOfYear =
	(datediff(dd,
	-- Case finds start of year
	case
	when	NextYrStart <= @date
	then	NextYrStart
	when	CurrYrStart <= @date
	then	CurrYrStart
	else	PriorYrStart
	end,@date)/7)+1
from
	(
	select
		-- First day of first week of prior year
		PriorYrStart =
		dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
		-- First day of first week of current year
		CurrYrStart =
		dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
		-- First day of first week of next year
		NextYrStart =
		dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
	from
		(
		select
			--Find Jan 4 for the year of the input date
			Jan4	= 
			dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
		) aa
	) a

return @WeekOfYear

end
go





-- Execute function on first day of first week of year from 1990 to 2030
select
	DT,
	ISO_WEEK_OF_YEAR = 
		dbo.F_ISO_WEEK_OF_YEAR(a.DT)
from
	(
	select DT = getdate()	union all
	select DT = convert(datetime,'1990/01/01')  union all
	select DT = convert(datetime,'1990/12/31')  union all
	select DT = convert(datetime,'1991/12/30')  union all
	select DT = convert(datetime,'1993/01/04')  union all
	select DT = convert(datetime,'1994/01/03')  union all
	select DT = convert(datetime,'1995/01/02')  union all
	select DT = convert(datetime,'1996/01/01')  union all
	select DT = convert(datetime,'1996/12/30')  union all
	select DT = convert(datetime,'1997/12/29')  union all
	select DT = convert(datetime,'1999/01/04')  union all
	select DT = convert(datetime,'2000/01/03')  union all
	select DT = convert(datetime,'2001/01/01')  union all
	select DT = convert(datetime,'2001/12/31')  union all
	select DT = convert(datetime,'2002/12/30')  union all
	select DT = convert(datetime,'2003/12/29')  union all
	select DT = convert(datetime,'2005/01/03')  union all
	select DT = convert(datetime,'2006/01/02')  union all
	select DT = convert(datetime,'2007/01/01')  union all
	select DT = convert(datetime,'2007/12/31')  union all
	select DT = convert(datetime,'2008/12/29')  union all
	select DT = convert(datetime,'2010/01/04')  union all
	select DT = convert(datetime,'2011/01/03')  union all
	select DT = convert(datetime,'2012/01/02')  union all
	select DT = convert(datetime,'2012/12/31')  union all
	select DT = convert(datetime,'2013/12/30')  union all
	select DT = convert(datetime,'2014/12/29')  union all
	select DT = convert(datetime,'2016/01/04')  union all
	select DT = convert(datetime,'2017/01/02')  union all
	select DT = convert(datetime,'2018/01/01')  union all
	select DT = convert(datetime,'2018/12/31')  union all
	select DT = convert(datetime,'2019/12/30')  union all
	select DT = convert(datetime,'2021/01/04')  union all
	select DT = convert(datetime,'2022/01/03')  union all
	select DT = convert(datetime,'2023/01/02')  union all
	select DT = convert(datetime,'2024/01/01')  union all
	select DT = convert(datetime,'2024/12/30')  union all
	select DT = convert(datetime,'2025/12/29')  union all
	select DT = convert(datetime,'2027/01/04')  union all
	select DT = convert(datetime,'2028/01/03')  union all
	select DT = convert(datetime,'2029/01/01')  union all
	select DT = convert(datetime,'2029/12/31')  union all
	select DT = convert(datetime,'2030/12/30')
	) a








CODO ERGO SUM

Edited by - Michael Valentine Jones on 01/18/2006 22:07:33

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 03/25/2009 :  06:58:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I've made some modification to deal with the year of 9999, if anyone needs that.
Also, then @Date >= NextYrStart, the only possible week number is 1, so there is no need to calculate that.
RETURNS TINYINT
AS
BEGIN
	RETURN	(
			SELECT	CASE
					WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7 
					WHEN @theDate >= '99990101' THEN 52
					WHEN NextYear <= @theDate THEN 0
					WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7
					ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7
				END + 1
			FROM	(
					SELECT	DATEADD(DAY, (DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,
						DATEADD(DAY, (DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
						DATEADD(DAY, (DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
					FROM	(
							SELECT	DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4
						) AS x
				) AS d
		)
END



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

huslayer
Starting Member

10 Posts

Posted - 08/31/2010 :  09:07:29  Show Profile  Reply with Quote
Public Function ISO_DateOfWeek( _
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes: Years less than 100 will be handled as
' two-digit years of our current year frame.
' Years less than zero returns a zero date.
' A weeknumber of zero returns the requested
' weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, CPH.

' The fourth of January is always included in
' the first week of year intYear.
Const cbytDayOfFirstWeek As Byte = 4
' Number of days in a week.
Const cbytDaysOfWeek As Byte = 7
' Month of January.
Const cbytJanuary As Byte = 1

Dim datDateOfFirstWeek As Date
Dim intISOMonday As Integer
Dim intISOWeekday As Integer
Dim intWeekdayOffset As Integer

' No specific error handling.
On Error Resume Next

If intYear > 0 Then
' Weekday of Monday.
intISOMonday = WeekDay(vbMonday, vbMonday)
' Date of fourth of January in year intYear.
datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
' Weekday of fourth of January in year intYear.
intISOWeekday = WeekDay(datDateOfFirstWeek, vbMonday)
' Calculate offset from Monday in first week of year intYear.
intWeekdayOffset = intISOMonday - intISOWeekday

' Weekday of requested weekday.
intISOWeekday = WeekDay(bytWeekday, vbMonday)
' Calculate offset from requested weekday in first week of year intYear.
intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
' Date of requested weekday in first week of year intYear.
datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)

' Date of requested weekday in requested week of year intYear.
datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
End If

ISO_DateOfWeek = datDateOfFirstWeek

End Function
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/31/2010 :  09:30:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is that a T-SQL function?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/31/2010 :  10:16:42  Show Profile  Reply with Quote
quote:
Originally posted by huslayer

Public Function ISO_DateOfWeek( _
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
...

Function ISO_DateOfWeek seems to have a completely different purpose than F_ISO_WEEK_OF_YEAR, besides not being written in SQL.


CODO ERGO SUM
Go to Top of Page

Adianjali
Starting Member

India
7 Posts

Posted - 01/24/2011 :  02:27:44  Show Profile  Reply with Quote
Below is the function i tried from this forum.
and i want to calculate week from monday to sunday
example--> 1st jan(2011) is saturday and 2nd jan(2011) is sunday is should come as 1st week of the year
then from 3rd jan to 9th jan to be 2nd week and so on
but when i used below function
it gives output as
for December 2010 last week it gives 52nd week and for 1st jan and 2nd jan also it gives 52nd week
but it should give 1st week according to the required condition
Can anyone help for the same


create function Find_Week_of_Year
(
@Date datetime
)
returns int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear int

select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
case
when NextYrStart <= @date
then NextYrStart
when CurrYrStart <= @date
then CurrYrStart
else PriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a

return @WeekOfYear

end




arjun.s
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 01/24/2011 :  04:16:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
;WITH cteCalendar
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 7 * Number)) / 7 * 7, 0) AS theMonday,
		1 + Number AS theWeek
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND number BETWEEN 0 AND 52
)
SELECT		theWeek,
		theMonday,
		DATEADD(DAY, 6, theMonday) AS theSunday
FROM		cteCalendar
WHERE		2011 IN (DATEPART(YEAR, theMonday), DATEPART(YEAR, DATEADD(DAY, 6, theMonday)))
ORDER BY	theWeek



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

khops
Starting Member

USA
1 Posts

Posted - 02/21/2011 :  14:18:38  Show Profile  Reply with Quote
This is the exact function I was looking for. The report I had developed was displaying Week 53 and skipping to Week 2. Problem is now resolved and shows Week 52, Week 1, Week 2...

Many thanks Michael.
Go to Top of Page

beslick
Starting Member

Australia
1 Posts

Posted - 09/15/2011 :  00:15:58  Show Profile  Reply with Quote
Just in case it's of any use to anybody, I slightly altered the original function suggested by Michael Valentine Jones in the original response. The only change was to determine and return the week year, as well as week of year. Thus the return is now a VARCHAR(7) of format YYYY-WK. This is handy for reporting purposes especially if data/figures are grouped by week, and therefore avoids potential of having week 1, 52, or 53 from prior/current/following year being added together incorrectly. Anyway, just sharing in case it helps out. many thanks to Michael for the original reply way back in week 3 of 2006 :-)



drop function F_ISO_WEEK_OF_YEAR
go
CREATE function F_ISO_WEEK_OF_YEAR
(
@Date datetime
)
returns varchar(7)
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear varchar(7)
select @WeekOfYear=
case when right(CONVERT(CHAR(7),@date,120),2) = '01' and myweek >= 52 then
cast(cast(CONVERT(CHAR(4),@date,120) as int)-1 as char(4))+'-'+right('00' + cast(myweek as varchar(2)),2)
when right(CONVERT(CHAR(7),@date,120),2) = '12' and myweek = 1 then
cast(cast(CONVERT(CHAR(4),@date,120) as int)+1 as char(4))+'-'+right('00' + cast(myweek as varchar(2)),2)
else CONVERT(CHAR(4),@date,120)+'-'+right('00' + cast(myweek as varchar(2)),2) end
--as myweekofyear

from
(
select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
(datediff(dd,
-- Case finds start of year
case
when NextYrStart <= @date
then NextYrStart
when CurrYrStart <= @date
then CurrYrStart
else PriorYrStart
end,@date)/7)+1 as myweek
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a
)z

return @WeekOfYear

end
go

Remember, if it was easy it wouldn't be fun!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/15/2011 :  16:11:28  Show Profile  Reply with Quote
beslick, I think what you are going for was already done here:
ISO Year Week Day of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515





CODO ERGO SUM
Go to Top of Page

miftakhul_terang
Starting Member

Indonesia
1 Posts

Posted - 03/29/2014 :  01:51:14  Show Profile  Reply with Quote
Sorry, ALL THIS FUNCTION NOT WORK for date '01/01/2011'(jan 1 2011)
the correct week is 2011-01, but result function is 2010-52
there are some mistake...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 03/29/2014 :  04:46:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't think so. 1st of January 2011 is a saturday.
Week number 1 (according to ISO) is the week (monday-sunday) that contains the first thursday.
And since 1st of January is a saturday, it is not week 1.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17611 Posts

Posted - 03/29/2014 :  05:23:45  Show Profile  Reply with Quote
If run this in SQL Server 2012 which as a ISO_WEEK function, it will also return 52

SELECT	DATEPART(ISO_WEEK, '2011-01-01')

----------- 
52

(1 row(s) affected)




KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000