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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 01/18/2006 :  22:08:27  Show Profile  Reply with Quote
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
create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
	@Date	datetime
returns		varchar(10)
returns the ISO 8601 Year Week Day of Week
in format YYYY-W01-D for the date passed.

declare @YearWeekDayOfWeek	varchar(10)

	--Format to form YYYY-W01-D
	@YearWeekDayOfWeek =
	right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) +
	YearStart =
	-- Case finds start of year
	when	NextYrStart <= @date
	then	NextYrStart
	when	CurrYrStart <= @date
	then	CurrYrStart
	else	PriorYrStart
	-- First day of first week of prior year
	PriorYrStart =
	-- First day of first week of current year
	CurrYrStart =
	-- First day of first week of next year
	NextYrStart =
	--Find Jan 4 for the year of the input date
	Jan4	= 
) aaa
) aa
) a

return @YearWeekDayOfWeek


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

	DT	= convert(varchar(10),DT,121),
	YR_START_DT_60 = 
	YR_START_DT_365 = 
	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:

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


Starting Member

2 Posts

Posted - 03/19/2008 :  15:57:56  Show Profile  Reply with Quote
Great! I searched other places for this but this is the best one of all. Thanks.
Go to Top of Page

Starting Member

1 Posts

Posted - 02/03/2012 :  07:53:31  Show Profile  Reply with Quote
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.

DT = convert(varchar(10),DT,121),
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

Go to Top of Page
  Previous Topic Topic Next 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.08 seconds. Powered By: Snitz Forums 2000