ISO Week In SQL Server
From Wiki
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
- 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 the following query on SQL server 2000 and up
- 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
- 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
