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


gioco al casinò online
2010-05-19
re: Get date from ISO week number
The purpose of this International Standard is to eliminate the risk of misinterpretation where numeric representation of dates and times are interchanged across national boundaries, and to avoid the confusion and other consequential errors or losses.

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.