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.

 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
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),
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 - 2008-03-19 : 15:57:56
Great! I searched other places for this but this is the best one of all. Thanks.
Go to Top of Page

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.

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

- Advertisement -