Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

ISO Week In SQL Server

From Wiki

Jump to: navigation, search

Sometimes you need to show the ISO week in SQL server but there was no built in way to calculate it until SQL server 2008 was released. In SQL Server 2000/2005 you could use the user defined function ISOweek which was in the SQL Server books on line. Here is what the function looks like

  1. CREATE FUNCTION ISOweek  (@DATE datetime)
  2. RETURNS int
  3. AS
  4. BEGIN
  5.    DECLARE @ISOweek int
  6.    SET @ISOweek= DATEPART(wk,@DATE)+1
  7.       -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
  8. --Special cases: Jan 1-3 may belong to the previous year
  9.    IF (@ISOweek=0)
  10.       SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
  11.          AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
  12. --Special case: Dec 29-31 may belong to the next year
  13.    IF ((DATEPART(mm,@DATE)=12) AND
  14.       ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
  15.       SET @ISOweek=1
  16.    RETURN(@ISOweek)
  17. END
  18. GO

Now run the following query on SQL server 2000 and up

  1. select dbo.ISOweek('20071231'),datepart(wk,'20071231')

As you can see SQL Server's wk part returns 53 while ISO week returns 1

If you are running SQL server 2008 then you can use DATEPART and the datepart argument isowk. Run the select statement below to see the result

  1. select datepart(isowk,'20071231'),datepart(wk,'20071231')

As you can see also here SQL Server's wk part returns 53 while isowk returns 1

Contributed by: --SQLDenis 15:57, 22 September 2008 (GMT)

Part of SQL Server Programming Hacks

Section Dates

595 Rating: 2.1/5 (80 votes cast)