ISO Week of Year Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

ISO Week of Year Function

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-18 : 19:36:52

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 06:58:39

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"

huslayer
Starting Member

10 Posts

Posted - 2010-08-31 : 09:07:29

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-31 : 09:30:02

Is that a T-SQL function?

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-31 : 10:16:42

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

Adianjali
Starting Member

7 Posts

Posted - 2011-01-24 : 02:27:44

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-24 : 04:16:13

[code];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[/code]

N 56°04'39.26"
E 12°55'05.63"

khops
Starting Member

1 Post

Posted - 2011-02-21 : 14:18:38

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.

beslick
Starting Member

1 Post

Posted - 2011-09-15 : 00:15:58

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!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-09-15 : 16:11:28

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

miftakhul_terang
Starting Member

1 Post

Posted - 2014-03-29 : 01:51:14

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-03-29 : 04:46:59

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-29 : 05:23:45

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources