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. |