Thinking outside the box

How to get a date from Year, week and weekday

This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below. And according to ISO, Monday is 1 and Sunday is 7.


CREATE FUNCTION dbo.fnGetDateFromYearWeekWeekday
(
    @Year INT,
    @Week INT,
    @Weekday INT
)
RETURNS DATETIME
AS
BEGIN
    RETURN CASE
                WHEN @Year < 1900 OR @Year > 9999 THEN NULL
                WHEN @Week < 1 OR @Week > 53 THEN NULL
                WHEN @Weekday < 1 OR @Weekday > 7 THEN NULL
                WHEN @Year = 9999 AND @Week = 52 And @Weekday > 5 THEN NULL
                WHEN DATEPART(YEAR, DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7 * 7, -4)) <> @Year THEN NULL
                ELSE DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7 * 7, @Weekday - 8)
            END
END
 

Print | posted on Tuesday, December 01, 2009 12:21 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]