First and Last day of week

Hi All
I have a report that brings back data based on the week a user selects.
The user will select a week number (e.g. week 1) then the report will automatically generate the first date (Monday) and last date of that week (Sunday), based on a whole week for the current year.
So, user selects week 3 for this year. The report generates the first date of the week: 16/01/2006 and last date of the week: 22/01/2006.

Note: I’m in the UK, so I am using the UK date format.

Try this:

Code/

Declare @.DOW As Char(10), @.weekdate Datetime, @.Monday DateTime, @.Sunday DateTime,

@.Working As Int, @.WeekNo Int

-- Find first day of year

Set @.WeekDate = (select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--Find a date in this week - any date

Set @.Working = @.WeekNo * 7

Select @.WeekDate = DateAdd(dd,@.Working,@.WeekDate)

Get day of week and subtract number of days to get the Monday

Set @.DOW = DateName(dw,@.Weekdate)

Set @.Monday = @.Weekdate

Set @.Monday = Case @.DOW

When 'Sunday' Then DateAdd(dd,-6,@.WeekDate)

When 'Saturday' Then DateAdd(dd,-5,@.WeekDate)

When 'Friday' Then DateAdd(dd,-4,@.WeekDate)

When 'Thursday' Then DateAdd(dd,-3,@.WeekDate)

When 'Wednesday' Then DateAdd(dd,-2,@.WeekDate)

When 'Tuesday' Then DateAdd(dd,-1,@.WeekDate)

Else @.Monday

End

Set @.Sunday = DateAdd(dd,6,@.Monday)

Select @.Monday, @.Sunday

/Code

You feed in @.WeekNo