sql - Adjust Date to match saved day of week -

i have table stores startdate , name of day of week start date falls on. don't know why, bad design didn't create , can't change it. of course, have dates don't match day of week. make worse, day of week correct , start date incorrect. need adjust dates each row's startdate falls on row's dayofweek. can assume startdate minimum value target date first [dayofweek] after set startdate.

so example have rows (8/23/10 mon, 8/29/10 sun):

startdate    dayofweek ----------------------- 2010-08-23   monday 2010-08-23   tuesday 2010-08-29   thursday 

in row 2 can see date supposed tuesday it's monday. need end this:

startdate    dayofweek ----------------------- 2010-08-23   monday 2010-08-24   tuesday 2010-09-02   thursday 

i struggle when working dates, sql not strongest skill either. thanks.

stealing geofftnz's setup, , hoping "clever" method thinking of:

declare @baddata table(startdate datetime, [dayofweek] varchar(20)) insert @baddata values('2010-08-23','monday') insert @baddata values('2010-08-23','tuesday') insert @baddata values('2010-08-29','thursday')  select * @baddata  ;with nums (     select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 ) update b set startdate = dateadd(day,nums.n,startdate)     @baddata b         inner join     nums         on             datename(weekday,dateadd(day,nums.n,startdate)) = [dayofweek]  select * @baddata 

for third row, i'm getting date in september, not august, think result right , sample result incorrect?