RMJCS Logo
 

ISO Week Number

 
 

ISO 8601 defines the first week of the year as the one that includes the first Thursday. The weeks are numbered starting with 1 through to 52 or 53. See the ISO 8601 entry in Wikipedia for more info. 

Prior to SQL Server 2008, the DATEPART function supported a 'week' argument but it's not an ISO week number; the result is based on January 1st and is also dependant on the setting of the DATEFIRST session setting, which by default sets Sunday to be the first day of the week. SQL Server 2008 supports a new 'isowk' or 'isoww' argument for DATEPART. BOL actually includes an ISO week function (dbo.ISOweek) in the CREATE FUNCTION topic, but this implementation requires SET DATEFIRST 1 which might not be appropriate in your environment.

The function below returns the ISO week number for the supplied date regardless of the DATEFIRST setting. I have a separate function for the ISO Weekday Number.

This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option).

 
     
 

dbo.udf_GetISOWeekNumberFromDate

 
 
CREATE FUNCTION dbo.udf_GetISOWeekNumberFromDate(@dtDate as DATETIME) RETURNS INT WITH RETURNS NULL ON NULL INPUT
/*
** Return the ISO week of the year regardless of the DATEFIRST session setting.
*/
AS
BEGIN

DECLARE @intISOWeekdayNumber INT
DECLARE @dtThisThursday DATETIME
DECLARE @dtFirstOfThisThursdaysYear DATETIME
DECLARE @intISOWeekdayNumberOfFirstOfThisThursdaysYear INT
DECLARE @dtFirstThursdayOfYear DATETIME
DECLARE @intISOWeekNumber INT

   -- Get the ISO week day number (Monday = 1) for our date.
   SET @intISOWeekdayNumber = (((DATEPART(dw, @dtDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1

   -- Get the date of the Thursday in this ISO week.
   SET @dtThisThursday = DATEADD(d,(4 - @intISOWeekdayNumber),@dtDate)

   -- Get the date of the 1st January for 'this Thursdays' year.
   SET @dtFirstOfThisThursdaysYear = CAST(CAST(YEAR(@dtThisThursday) AS CHAR(4)) + '-01-01' AS DATETIME)

   SET @intISOWeekdayNumberOfFirstOfThisThursdaysYear = (((DATEPART(dw, @dtFirstOfThisThursdaysYear) - 1) + (@@DATEFIRST - 1)) % 7) + 1

   -- Get the date of the first Thursday in 'this Thursdays' year.
   -- The year of which the ISO week is a part is the year of this date.     
   IF (@intISOWeekdayNumberOfFirstOfThisThursdaysYear in (1,2,3,4))
      SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear),@dtFirstOfThisThursdaysYear)
   ELSE
      SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear + 7),@dtFirstOfThisThursdaysYear)

   -- Work out how many weeks from the first Thursday to this Thursday.
   SET @intISOWeekNumber = DATEDIFF(d,@dtFirstThursdayOfYear,@dtThisThursday)/7+1
   
   RETURN @intISOWeekNumber

END
GO

-- Tests / Examples
SET DATEFIRST 1 -- First day of week is Monday.
SELECT
    dbo.udf_GetISOWeekNumberFromDate('1753-01-01') AS MinDate,
    dbo.udf_GetISOWeekNumberFromDate('9999-12-31') AS MaxDate,
    dbo.udf_GetISOWeekNumberFromDate('1900-01-01') AS ZeroDate
SET DATEFIRST 7 -- First day of week is Sunday.
SELECT
    dbo.udf_GetISOWeekNumberFromDate('1753-01-01') AS MinDate,
    dbo.udf_GetISOWeekNumberFromDate('9999-12-31') AS MaxDate,
    dbo.udf_GetISOWeekNumberFromDate('1900-01-01') AS ZeroDate
GO
 
     
Copyright 2007-2014 RMJCS Ltd