/****** Object:  User Defined Function dbo.isoyear    Script Date: 12/1/2005 11:50:34 PM ******/
/**
* returns the first day of the iso-calendar for a given date
*/
CREATE FUNCTION isoyear(@date datetime)
RETURNS datetime as
BEGIN
    DECLARE @month int, @day int, @year int, @dw_num int, @result datetime
    SET @month = DATEPART(m, @date)
    SET @day = DATEPART(d, @date)
    SET @year = DATEPART(year, @date)
    SET @dw_num = DATEPART(dw, @date)
    --if at the end of decemeber make sure input date not in first week of next year
    IF((@month = 12) AND (@day >= 28))
    BEGIN
        --if the 31st of dec is on a monday through wednesday, then its a part of the first week
        IF( ((@day=29) AND (@dw_num IN (2))) OR
            ((@day=30) AND (@dw_num IN (2, 3))) OR
            ((@day=31) AND (@dw_num IN (2, 3, 4))))
        BEGIN
            DECLARE @lastDay datetime, @lastDw int            
            SET @lastDay  = CONVERT(datetime, '12/31/' + CONVERT(varchar(4), @year))
            SET @lastDw = DATEPART(dw, @lastDay)
            IF((1 < @lastDw) and (@lastDw < 5))
            BEGIN
                --then we are in the first week of the year
                SET @result = DATEADD(d, (2- @lastDw), @lastDay)
                RETURN @result
            END
        END
    END
    --start search for beginning of the year in january
    --if input date falls in jan and is part of last year, recall function w/dec date
    IF((@month = 1) and (@day <=3))
    BEGIN
        IF( ((@day=1) AND (@dw_num IN (6, 7, 1))) OR
            ((@day=2) AND (@dw_num IN (7, 1))) OR
            ((@day=3) AND (@dw_num IN (1))))
        BEGIN
            RETURN dbo.isoyear(CONVERT(datetime, '12/31/' + CONVERT(varchar(4), @year -1)))
        END
    END
    DECLARE @firstDay datetime, @firstDw int, @diff int
    SET @firstDay = CONVERT(datetime, '01/01/' + CONVERT(varchar(4), @year))
    SET @firstDw = DATEPART(dw, @firstDay)
    -- if the 1st is a friday or later then it is apart of last year
    IF(@firstDw =1)
    BEGIN
        SET @firstDw =8
    END
    IF(@firstDw > 5)
    BEGIN
        SET @diff = (7 - @firstDw +2)
    END
    ELSE --otherwise it's apart of the first week
    BEGIN
        SET @diff = (2 - @firstDw)
    END
    SET @result = DATEADD(d, @diff, @firstDay)
    return @result
END
GO