Thinking outside the box

Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx.
The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx.


CREATE FUNCTION dbo.fnISOMonday
(
    @theYear SMALLINT,
    @theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
                FROM    (
                            SELECT  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, @theYear - 1900, 3) AS Jan4
                                        WHERE   @theYear BETWEEN 1900 AND 9999
                                                AND @theWeek BETWEEN 1 AND 53
                                    ) AS x
                        ) AS d
                WHERE   DATEADD(DAY, 7 * @theWeek - 7, CurrentYear) < NextYear
            )
END

Print | posted on Tuesday, January 26, 2010 10:57 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]