How to Get or Return the ISO Week Number from a Date in SQL Server

Tutorialsrack 25/02/2021 SQL SQL Server

In this article, you’ll learn how to get or return the ISO week number from a date in SQL Server. In this article, we use the two ways to get the ISO week number from a date. One is the DATEPART() function and the second one is a user-defined function. You can use the DATEPART() function to get the week number of a year from a date and you can use the iso_week as the first parameter. And you can also use alternative intervals like ISOWK or ISOWW and you will get the same output. 

First let’s take a glance at what ISO week is, from Wikipedia:

Week date representations are within the format as shown below.

YYYY-Www or YYYYWww

YYYY-Www-D or YYYYWwwD

Where,

• [YYYY] indicates the ISO year which is slightly different from the calendar year,
• [Www] is that the week number prefixed by the letter ‘W’, from W01 through W53 and, 
• the [D] is that the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. 

There are mutually equivalent definitions for week 01:

• the week with the year’s first Thursday in it,
• the week with 4 January in it,
• the first week with the majority (four or more) of its days within the starting year, and
• the week starting with the Monday within the period 29 December to 4 January.

If 1 January is on a Monday, Tuesday, Wednesday, or Thursday, it's in week 01. If 1 January is on a Friday, Saturday, or Sunday, it's in week 52 or 53 of the previous year.

This means that once you extract the week number from a date, you'll get different results depending on whether you’re using the Gregorian calendar or the ISO 8601 date and time standard.

You can learn more about ISO Week Here: Click Here

Here are the examples to extract the week number from a date in SQL Server.

Example 1

DECLARE @date date = '2021-01-01';
SELECT DATEPART(iso_week, @date) AS 'ISO WEEK NUMBER';

In the above example, the date is 1st January 2021, but you will see the output that is the 53rd week of 2020 in terms of ISO.

Comparison with Gregorian Year

Now let's take another example of ISO WEEK to compare with the Gregorian week:

Example 2

DECLARE @date date = '2021-01-01';
SELECT 
  DATEPART(week, @date) AS 'GREGORIAN WEEK' ,
  DATEPART(iso_week, @date) AS 'ISO WEEK';

So in the above example, you can see that the same date can have a different week number depending on whether you’re using the Gregorian calendar or the ISO Date and Time standard.

Now let’s take another example of ISO Week to compare with the Gregorian week but this time we take the year-end date:

Example 3

DECLARE @date date = '2020-12-27';
SELECT 
  DATEPART(week, @date) AS 'GREGORIAN WEEK',
  DATEPART(iso_week, @date) AS 'ISO WEEK';

Alternative Intervals for ISO Week

You can also use the alternative Intervals as a parameter for getting ISO week numbers:

Example 4

DECLARE @date date = '2021-01-01';
SELECT 
    DATEPART(ISO_WEEK, @date) AS 'ISO WEEK',
    DATEPART(ISOWK, @date) AS 'ISOWK',
    DATEPART(ISOWW, @date) AS 'ISOWW';

If you are using SQL Server 2000/2005, then you can use this user-defined function to get the ISO Week Number from a date.

Example

CREATE FUNCTION 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
GO

Now run this query and you’ll get the ISO Week.

SELECT dbo.ISOweek('2021-01-01') as 'ISO WEEK'

I hope this article will help you to understand how to get or return the ISO week number from a date in SQL Server (T-SQL).

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts