ISO Year Week Day of Week Function

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 Year Week Day of Week Function

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-18 : 22:08:27

Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.

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, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.


drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
GO
create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
	(
	@Date	datetime
	)
returns		varchar(10)
as
/*
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK
returns the ISO 8601 Year Week Day of Week
in format YYYY-W01-D for the date passed.
*/
begin

declare @YearWeekDayOfWeek	varchar(10)

Select
	--Format to form YYYY-W01-D
	@YearWeekDayOfWeek =
	convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+
	right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) +
	'-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1) 
from
(
select
	YearStart =
	-- Case finds start of year
	case
	when	NextYrStart <= @date
	then	NextYrStart
	when	CurrYrStart <= @date
	then	CurrYrStart
	else	PriorYrStart
	end
from
(
select
	-- First day of first week of prior year
	PriorYrStart =
	dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690),
	-- First day of first week of current year
	CurrYrStart =
	dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690),
	-- First day of first week of next year
	NextYrStart =
	dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.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))
) aaa
) aa
) a

return @YearWeekDayOfWeek

end
go


-- Execute function on first day, first day+60,
-- and first day+364 for years from 1990 to 2030.

select
	DT	= convert(varchar(10),DT,121),
	YR_START_DT = 
		dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT),
	YR_START_DT_60 = 
		dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60),
	YR_START_DT_365 = 
		dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364)
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

Function Test Results:


DT         YR_START_DT YR_START_DT_60 YR_START_DT_364 
---------- ----------- -------------- --------------- 
2006-01-18 2006-W03-3  2006-W11-7     2007-W03-3
1990-01-01 1990-W01-1  1990-W09-5     1991-W01-1
1990-12-31 1991-W01-1  1991-W09-5     1992-W01-1
1991-12-30 1992-W01-1  1992-W09-5     1992-W53-1
1993-01-04 1993-W01-1  1993-W09-5     1994-W01-1
1994-01-03 1994-W01-1  1994-W09-5     1995-W01-1
1995-01-02 1995-W01-1  1995-W09-5     1996-W01-1
1996-01-01 1996-W01-1  1996-W09-5     1997-W01-1
1996-12-30 1997-W01-1  1997-W09-5     1998-W01-1
1997-12-29 1998-W01-1  1998-W09-5     1998-W53-1
1999-01-04 1999-W01-1  1999-W09-5     2000-W01-1
2000-01-03 2000-W01-1  2000-W09-5     2001-W01-1
2001-01-01 2001-W01-1  2001-W09-5     2002-W01-1
2001-12-31 2002-W01-1  2002-W09-5     2003-W01-1
2002-12-30 2003-W01-1  2003-W09-5     2004-W01-1
2003-12-29 2004-W01-1  2004-W09-5     2004-W53-1
2005-01-03 2005-W01-1  2005-W09-5     2006-W01-1
2006-01-02 2006-W01-1  2006-W09-5     2007-W01-1
2007-01-01 2007-W01-1  2007-W09-5     2008-W01-1
2007-12-31 2008-W01-1  2008-W09-5     2009-W01-1
2008-12-29 2009-W01-1  2009-W09-5     2009-W53-1
2010-01-04 2010-W01-1  2010-W09-5     2011-W01-1
2011-01-03 2011-W01-1  2011-W09-5     2012-W01-1
2012-01-02 2012-W01-1  2012-W09-5     2013-W01-1
2012-12-31 2013-W01-1  2013-W09-5     2014-W01-1
2013-12-30 2014-W01-1  2014-W09-5     2015-W01-1
2014-12-29 2015-W01-1  2015-W09-5     2015-W53-1
2016-01-04 2016-W01-1  2016-W09-5     2017-W01-1
2017-01-02 2017-W01-1  2017-W09-5     2018-W01-1
2018-01-01 2018-W01-1  2018-W09-5     2019-W01-1
2018-12-31 2019-W01-1  2019-W09-5     2020-W01-1
2019-12-30 2020-W01-1  2020-W09-5     2020-W53-1
2021-01-04 2021-W01-1  2021-W09-5     2022-W01-1
2022-01-03 2022-W01-1  2022-W09-5     2023-W01-1
2023-01-02 2023-W01-1  2023-W09-5     2024-W01-1
2024-01-01 2024-W01-1  2024-W09-5     2025-W01-1
2024-12-30 2025-W01-1  2025-W09-5     2026-W01-1
2025-12-29 2026-W01-1  2026-W09-5     2026-W53-1
2027-01-04 2027-W01-1  2027-W09-5     2028-W01-1
2028-01-03 2028-W01-1  2028-W09-5     2029-W01-1
2029-01-01 2029-W01-1  2029-W09-5     2030-W01-1
2029-12-31 2030-W01-1  2030-W09-5     2031-W01-1
2030-12-30 2031-W01-1  2031-W09-5     2032-W01-1

(43 row(s) affected)

CODO ERGO SUM

evbstv
Starting Member

2 Posts

Posted - 2008-03-19 : 15:57:56

Great! I searched other places for this but this is the best one of all. Thanks.

Lxocram
Starting Member

1 Post

Posted - 2012-02-03 : 07:53:31

The test-code is not language setting proof, add conversion hint ,121

Msg 242, Level 16, State 3, Line 6
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

-- Execute function on first day, first day+60,
-- and first day+364 for years from 1990 to 2030.

select
DT = convert(varchar(10),DT,121),
YR_START_DT =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT),
YR_START_DT_60 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60),
YR_START_DT_365 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364)
from
(
select DT = getdate() union all
select DT = convert(datetime,'1990/01/01',121) union all
select DT = convert(datetime,'1990/12/31',121) union all
select DT = convert(datetime,'1991/12/30',121) union all
select DT = convert(datetime,'1993/01/04',121) union all
select DT = convert(datetime,'1994/01/03',121) union all
select DT = convert(datetime,'1995/01/02',121) union all
select DT = convert(datetime,'1996/01/01',121) union all
select DT = convert(datetime,'1996/12/30',121) union all
select DT = convert(datetime,'1997/12/29',121) union all
select DT = convert(datetime,'1999/01/04',121) union all
select DT = convert(datetime,'2000/01/03',121) union all
select DT = convert(datetime,'2001/01/01',121) union all
select DT = convert(datetime,'2001/12/31',121) union all
select DT = convert(datetime,'2002/12/30',121) union all
select DT = convert(datetime,'2003/12/29',121) union all
select DT = convert(datetime,'2005/01/03',121) union all
select DT = convert(datetime,'2006/01/02',121) union all
select DT = convert(datetime,'2007/01/01',121) union all
select DT = convert(datetime,'2007/12/31',121) union all
select DT = convert(datetime,'2008/12/29',121) union all
select DT = convert(datetime,'2010/01/04',121) union all
select DT = convert(datetime,'2011/01/03',121) union all
select DT = convert(datetime,'2012/01/02',121) union all
select DT = convert(datetime,'2012/12/31',121) union all
select DT = convert(datetime,'2013/12/30',121) union all
select DT = convert(datetime,'2014/12/29',121) union all
select DT = convert(datetime,'2016/01/04',121) union all
select DT = convert(datetime,'2017/01/02',121) union all
select DT = convert(datetime,'2018/01/01',121) union all
select DT = convert(datetime,'2018/12/31',121) union all
select DT = convert(datetime,'2019/12/30',121) union all
select DT = convert(datetime,'2021/01/04',121) union all
select DT = convert(datetime,'2022/01/03',121) union all
select DT = convert(datetime,'2023/01/02',121) union all
select DT = convert(datetime,'2024/01/01',121) union all
select DT = convert(datetime,'2024/12/30',121) union all
select DT = convert(datetime,'2025/12/29',121) union all
select DT = convert(datetime,'2027/01/04',121) union all
select DT = convert(datetime,'2028/01/03',121) union all
select DT = convert(datetime,'2029/01/01',121) union all
select DT = convert(datetime,'2029/12/31',121) union all
select DT = convert(datetime,'2030/12/30',121)
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources