Facility9

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
*/

Which is then used to feed report parameters.