Here's some SQL code to change any date into the date for a particular day of that week. I'm making a report in which the business wants to see values aggregated by week-ending date (aka Friday). So all the dates have to be change dinto the friday of that week. The DATEADD formula below will do that.

DECLARE @sunday AS SMALLDATETIME
 SET @sunday = '8/26/2012'
DECLARE @friday AS SMALLDATETIME
 SET @friday = '8/31/2012'
DECLARE @saturday AS SMALLDATETIME
 SET @saturday = '9/1/2012'

SELECT 
 DATEADD(day, (DATEPART(dw, @sunday)-6)*-1, @sunday),
 DATEADD(day, (DATEPART(dw, @friday)-6)*-1, @friday),
 DATEADD(day, (DATEPART(dw, @saturday)-6)*-1, @saturday)