Peter Larsson Blog

Patron Saint of Lost Yaks

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
 

Legacy Comments


Sirga
2010-03-03
re: How to get a date from Year, week and weekday
It returns incorrect result for the first friday 2010 (for example).
Return is 2010-01-08
but must be 2010-01-01

Peso
2010-03-04
re: How to get a date from Year, week and weekday
No, it is NOT the first friday. It returns the date for friday of iso week number 1.

jbooker
2010-11-04
re: How to get a date from Year, week and weekday
Peso,

What changes need to mbe made to return calendar weeks rather than ISO weeks?

Thanks,
Josh

Peso
2010-11-04
re: How to get a date from Year, week and weekday
"DATEDIFF(DAY, 4" denotes Thursday (4 is thursday according to ISO).
ISO week number 1 is the first week (monday to sunday) containing the first thursday of the year.

If you change this to, say 7 for sunday, it would work in a similar way as ISO but full week is if you also change "@Weekday - 8" to a new number for the weekday you want to use instead.

Peso
2010-11-04
re: How to get a date from Year, week and weekday
If you are after January 1st to January 7th as the first week always,
use

SELECT 1 + (DATEPART(DAYOFYEAR, GETDATE()) - 1) / 7 AS Weeknumber

jbooker
2010-11-04
re: How to get a date from Year, week and weekday
Thanks. I'll have to do some testing. I want week defined the same as datepart(wk...with respect to week one. Not sure whether built-in datepart() week one is full 7 days or if it ends on first saturday. Assuming @@datefirst = 1 (sunday)

Can you clarify the effect of changing '@weekday -8' ? I gather it has something to do with finding the first full week.

Josh