get date by weekOfYear

Question

Hello,

I need Week-Start-Date and Week-End-Date for particular Week number in a year (vb.net or SQL Server)

For example, if weeknumber=1 and year 2009, I should get:

StartDate=1/1/2009 EndDate=1/3/2009

if weeknumber=2 and year 2009, then:

StartDate=1/4/2009 EndDate=1/10/2009

Actually I got week number by using datepart(wk, Date) in Query then I grouped by week number. Now I need start date and End Date for the grouped Week Number.

-- Raheel

Answer 1

Are you looking for something like this:

Dim day As DayOfWeek = DateTime.Now.DayOfWeek
Dim days As Integer = day - DayOfWeek.Monday
Dim startDate As DateTime = DateTime.Now.AddDays(-days)
Dim endDate As DateTime = startDate.AddDays(6)

You can change it, of course, so that the week starts with any day you think it should (Sun, Mon?)

Answer 2

It might be worth considering a Calendar Table.

Answer 3

Yeah, what day does the week start? Sunday, or Monday?

datepart( dw, date) returns the day of the week (1 to 7), but "[t]he number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week."

But it'll return the right thing, assuming your database is properly set up.

OK, if it returns 1, we're on the first day, or more generally, the first day is

1 - datepart( dw, date) days before our date

If it returns 7 we're at the last day of the week, or more generally, the last day is

7 - datepart( dw, date ) days after our date

We use dateadd( dd, n, date) to get a date n days from our date, so:

select
 date_column, 
 datepart(wk, date_column ) as week_number, 
 dateadd( dd, 1 - datepart( dw, date_column ),date_column ) as week_start, 
 dateadd( dd, 7 - datepart( dw, date_column ), date_column ) as week_end
from table ;

gives us what we need.

Note that the first day of the first week of a year night be in the previous calendar year, because by definition a week starts on (Sunday or Monday or whatever you've set it to be), but a year can start on any day of the week. Similarly, the last day of the last week of the year might be in the next calendar year.

Answer 4

TRY THIS TSQL:

DECLARE @Year int
DECLARE @WeekNo int
SET @Year = 2009
SET @WeekNo = 1

DECLARE @DaysInWeekOne int
DECLARE @FirstOfJan smalldatetime
DECLARE @ThirtyFirstOfDec smalldatetime
DECLARE @StartDateDayOfYear int
DECLARE @EndDateDayOfYear int

--GET THE START AND END OF THE YEAR
SELECT
    @ThirtyFirstOfDec = Cast(('12/31/' + Cast(@Year as varchar)) as smalldatetime)
,   @FirstOfJan = Cast(('01/01/' + Cast(@Year as varchar)) as smalldatetime)

--GET THE AMOUNT OF DAYS IN WEEK ONE
SELECT
    @DaysInWeekOne = 7 - (DatePart(dw, @FirstOfJan) - 1)

--GET THE START AND END DAYOFYEAR VALUES FOR THE GIVEN WEEK
SELECT
    @StartDateDayOfYear = CASE WHEN ((@WeekNo-1)*7)-(7-@DaysInWeekOne) < 0 THEN 0 ELSE ((@WeekNo-1)*7)-(7-@DaysInWeekOne) END
,   @EndDateDayOfYear = ((@WeekNo-1)*7)-(7-@DaysInWeekOne) + 6

--SELECT THE START DATE AND END DATE MAKING SURE IF THE COUNT RUNS OVER THE YEAR THAT WE SHOW THE 31/12
SELECT
    @Year As mYear
,   @WeekNo As WeekNo
,   dateadd(dd, @StartDateDayOfYear, (@FirstOfJan)) As StartDate
,   CASE 
     WHEN dateadd(dd, @EndDateDayOfYear,   (@FirstOfJan)) > @ThirtyFirstOfDec THEN @ThirtyFirstOfDec 
     ELSE dateadd(dd, @EndDateDayOfYear, @FirstOfJan) 
    END As EndDate