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 |