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

Lxocram
Starting Member

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

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


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.06 seconds. Powered By: Snitz Forums 2000