Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# 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
3/3/2010 11:25 AM | Sirga
Gravatar

# 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.
3/4/2010 12:08 AM | Peso
Gravatar

# 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
11/4/2010 1:49 PM | jbooker
Gravatar

# 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.
11/4/2010 2:01 PM | Peso
Gravatar

# 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
11/4/2010 2:04 PM | Peso
Gravatar

# 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
11/4/2010 9:51 PM | jbooker
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET