Getting the ISO Week and Year

Long story short: I need the ISO Week. We need to be able to make sure that when we’re reporting, we’re pulling data for the full week (which happens to coincide with ISO Week at my employer). The problem with using an existing function is that the existing functions just return the week and not the full year + week combination. I used the function from the Less Than Dot wiki as a starting point and added my own code to create the full YYYYWW string to give me an ISO Week. This isn’t actually in the ISO standard format for reporting the ISO week, but I also don’t care so long as I can use the ISOWeek for effective querying. Enjoy``` ALTER FUNCTION ISOweek (@DATE DATETIME) RETURNS VARCHAR(6) AS BEGIN DECLARE @ISOweek INT, @year INT, @rVal VARCHAR(6) ;

SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yyyy, @DATE) AS CHAR(4)) + '0104') ;
SET @year = DATEPART(yyyy, @DATE) ;

SET @rVal = CAST(@year AS VARCHAR(4)) + RIGHT('00' + CAST(@ISOWeek AS VARCHAR(2)), 2)

--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek = 0) 
  BEGIN
    SET @rVal = dbo.ISOweek(CAST(DATEPART(yyyy, @DATE) - 1 AS CHAR(4))
                               + '12'
                               + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2)))
                               + 1 ;
  END

--Special case: Dec 29-31 may belong to the next year
IF (
    (DATEPART(mm, @DATE) = 12)
    AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
   ) 
  BEGIN
    SET @ISOweek = 1 ;
    SET @year = DATEPART(yyyy, @DATE) + 1 ;
    
    SET @rVal = CAST(DATEPART(yyyy, @DATE) + 1 AS VARCHAR(4)) + '01'
  END

RETURN @rVal ;

END GO

This lets me do some snazzy reporting hackery like this: SELECT MIN(c2.[Date]) AS StartDate, MAX(c2.[Date]) AS EndDate FROM dbo.Calendar AS c INNER JOIN dbo.Calendar AS c2 ON c.ISOWeek = c2.ISOWeek WHERE c.[Date] = ‘20091229’ ;

/* StartDate EndDate

---

2009-12-27 00:00:00.000 2010-01-02 00:00:00.000 */