Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Extended ISO week function

CREATE FUNCTION    dbo.fnISOWEEK
(
    @theDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
    RETURN (
                SELECT CASE
                            WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7
                            WHEN @theDate >= '99990101' THEN 52
                            WHEN NextYear <= @theDate THEN 0
                            WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7
                            ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7
                        END + 1
                FROM    (
                            SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,
                                    DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
                                    DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
                            FROM    (
                                        SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4
                                    ) AS x
                        ) AS d
            )
END

Print | posted on Wednesday, May 27, 2009 9:52 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET