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