Peter Larsson Blog

Patron Saint of Lost Yaks

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

Legacy Comments


Primulamod
2010-08-23
re: Get date from ISO week number
Overall, great function.

With one flaw - this does not work for the max date:
Year = 9999
Week = 52

The next year calculation gives an overflow error.

jheide
2010-11-24
re: Get date from ISO week number
Work perfect. Thanks.

Scott
2011-05-02
re: Get date from ISO week number
Excellent, just what I needed.

Vivian
2012-07-31
re: Get date from ISO week number
Hi everybody!

I am very much an SQL newbie.
is it possible to include british time format on this function?
Also, is there a date wildcard in here somewhere?

Thank you for any help you guys can give.
:)

Peso
2012-07-31
re: Get date from ISO week number
I am not sure what you mean. Dates have no format inside SQL Server.
Only when you present and display the value.