Monday 30 March 2009

Get first day of a given week number

Today, we run into a small question: How do I get the first day of a given week number? Well, we've had some troubles before with Microsofts date calculations (e.g. in MS Access). The problems occurred on the occassions when a year got 53 weeks. So we wrote some custom code to get a correct weeknumber.
Now the question is revert, we need the first day of the week, based on a weeknumber and a year. On the internet, there were some samples, but that were to much lines of codes.
So, this is a short version:

Private Function GetFirstDayOfTheWeek(year As integer, week As integer) As Date

Dim January4th As Date = DateSerial(year, 1, 4)
Dim January1th As Date = New Date(year), 1, 1)
Dim DayOfInterest As Date
Dim FirstDayWeek1 As Date

FirstDayWeek1 = January4th.AddDays(1).AddDays(-Weekday(January4th, FirstDayOfWeek.Monday))
DayOfInterest = January1th.AddDays(((week - 1) * 7 - DateDiff(DateInterval.DayOfYear, FirstDayWeek1, January1th, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays)))

Return DayOfInterest

End Function

If you need some other date calculation routines, just let me know.


Unknown said...

Would love to see a T-SQL version of this

Rob said...

Hi, I have done some T-SQL programming in the past, but it's not accurate. However, if you are familiar with it, it would be easy to rewrite this (little) piece of code into a T-SQL function.