The US system has weeks from Sunday through Saturday, and partial weeks at the beginning and the end of the year. An advantage is that no separate year numbering like the ISO year is needed, while correspondence of lexicographical order and chronological order is preserved.
However, in Europe, we use ISO8601, where the week starts on Monday.This system is used (mainly) in government and business for fiscal periods.
There are mutually equivalent descriptions of week 01:
-the week with the year's first Thursday in it (the formal ISO definition),
-the week with 4 January in it,
-the first week with the majority (four or more) of its days in the starting year, and
-the week starting with the Monday in the period 29 December - 4 January.
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year. 28 December is always in the last week of its year. The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.
The ISO week-numbering year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The ISO week-numbering year number deviates from the number of the calendar year (Gregorian year) on a Friday, Saturday, and Sunday, or a Saturday and Sunday, or just a Sunday, at the start of the calendar year (which are at the end
of the previous ISO week-numbering year) and a Monday, Tuesday and Wednesday, or a Monday and Tuesday, or just a Monday, at the end of the calendar year (which are in week 01 of the next ISO week-numbering year).
For Thursdays, the ISO week-numbering year number is always equal to the calendar year number.
In SQL Server SELECT DATEPART(wk,'2010-01-01') results in weeknummer 1 while it should be week 53.
Untill version 2008 ISO8601 is not supported.
In MS SQL Server 2008 you can use
SELECT DATEPART(isowk,'2010-01-01') which results in week 53.
There are mutually equivalent descriptions of week 01:
In older versions (SQL 2005, SQL 200) you have to create a function to use ISOweeks:
CREATE FUNCTION [dbo].[ISOweek] (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
This is an example from SQL Book Online by Microsoft.
However, the function relies on the first day of the week being a monday.
Before testing you should set the first day on monday by using:
SET DATEFIRST 1
After that you can check it by:
select
Date,
dbo.ISOWEEK(a.Date) ISOWEEK
from
(
select Date = getdate() union
select Date = convert(datetime,'2010/01/01')
) a
The function below also works independent of the DATEFIRST setting:
create function dbo.ISOWEEK2
(
@Date datetime
)
returns int
as
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
You can check it by:
SET DATEFIRST 7
select
Date,
dbo.ISOWEEK(a.Date) ISOWEEK,
dbo.ISOWEEK2(a.Date) ISOWEEK2
from
(
select Date = getdate() union
select Date = convert(datetime,'2010/01/01')
) a
results for 2010/01/01 in:
ISOWEEK = 52
ISOWEEK2 = 53
SET DATEFIRST 1
select
Date,
dbo.ISOWEEK(a.Date) ISOWEEK,
dbo.ISOWEEK2(a.Date) ISOWEEK2
from
(
select Date = getdate() union
select Date = convert(datetime,'2010/01/01')
) a
results for 2010/01/01 in:
ISOWEEK = 53
ISOWEEK2 = 53
