Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# 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.
5/19/2010 11:21 AM | gioco al casinò online
Gravatar

# 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.
8/23/2010 11:57 PM | Primulamod
Gravatar

# re: Get date from ISO week number

Work perfect. Thanks.
11/24/2010 10:08 AM | jheide
Gravatar

# re: Get date from ISO week number

Excellent, just what I needed.
5/2/2011 7:36 AM | Scott
Gravatar

# 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.
:)
7/31/2012 11:34 AM | Vivian
Gravatar

# 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.
7/31/2012 2:45 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET