Adjust Date to match saved day of week

Question

I have a table that stores a StartDate and the name of the day of week that start date falls on. I don't know why, it is bad design but I didn't create it and can't change it. So of course, now we have some dates that don't match the day of week. To make it worse, the day of week is correct and the start date is incorrect. So what I need to do is adjust the dates so that each row's StartDate falls on that row's DayOfWeek. We can assume StartDate is always the minimum value so the target date will be the first [DayOfWeek] after the currently set StartDate.

So for example I have rows that look like this (8/23/10 was a Mon, 8/29/10 was a Sun):

StartDate    DayOfWeek
-----------------------
2010-08-23   Monday
2010-08-23   Tuesday
2010-08-29   Thursday

In row 2 you can see the date is supposed to be a Tuesday but it's actually a Monday. I need to end up with this:

StartDate    DayOfWeek
-----------------------
2010-08-23   Monday
2010-08-24   Tuesday
2010-09-02   Thursday

I always struggle when working with dates, but SQL is also not my strongest skill either. Thanks.

geofftnz · Answer 1 · 2011-05-11T01:39:01.183

There will be a clever way of doing this and a "let's just bash some data out" way. Here is the latter:

-- here's our bad data we want to fix:
declare @baddata table(StartDate datetime, [DayOfWeek] varchar(20))
insert into @baddata values('2010-08-23','Monday')
insert into @baddata values('2010-08-23','Tuesday')
insert into @baddata values('2010-08-29','Thursday')

-- we need to create a table containing valid date+day pairs for the 
-- range of our bad data

-- find max and min dates from our bad data
declare @MinDate datetime
declare @MaxDate datetime

select @MinDate = min(StartDate), @MaxDate = max(StartDate) from @baddata

-- offset max date by 7 days (which is the most we'll need to correct the date by)
set @MaxDate = dateadd(day,7,@MaxDate)

-- create a table matching dates to days
declare @dates table([Date] Datetime, [DayOfWeek] varchar(20))

declare @i int

-- populate the table with enough days to cover the range of your bad data
set @i = 0
while @i <= datediff(day,@MinDate, @MaxDate)
begin
    insert into @dates
    select dateadd(day, @i, @MinDate), datename(dw,dateadd(day, @i, @MinDate))

    set @i = @i + 1
end

-- show us our table
select * from @dates


-- update the ones with incorrect days
update bd
set
    bd.StartDate =  ( -- find the next date with a matching day
        select top 1
            d.[Date]
        from
            @dates d
        where
            d.[DayOfWeek] = bd.[DayOfWeek] and
            d.[Date] >= bd.StartDate
        order by
            d.[Date]
    )
from
    @baddata bd
        inner join @dates d on 
            d.[Date] = bd.StartDate
where
    bd.[DayOfWeek] != d.[DayOfWeek] -- date names don't match

select * from @baddata

Thanks a lot geofftnz. That seems to accomplish the goal just fine. I prefer Damien's approach because of the conciseness and it seems more efficient if the date range is large. I appreciate your help and your commenting. — xr280xr, May 11 '11 at 14:04
Yeah, Damien's approach looks better, especially if it is going to be run on a regular basis. If it's a one-off fixup job, then it doesn't matter so much (unless of course we're talking about queries that take minutes or hours to run). — geofftnz, May 11 '11 at 23:43

score 3 · Accepted Answer · answered May 11 '11 at 06:23

Stealing geofftnz's setup, and hoping this is the "clever" method he was thinking of:

declare @baddata table(StartDate datetime, [DayOfWeek] varchar(20))
insert into @baddata values('2010-08-23','Monday')
insert into @baddata values('2010-08-23','Tuesday')
insert into @baddata values('2010-08-29','Thursday')

select * from @baddata

;with Nums as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
)
update b
set StartDate = DATEADD(day,Nums.n,StartDate)
from
    @baddata b
        inner join
    Nums
        on
            DATENAME(weekday,DATEADD(day,Nums.n,StartDate)) = [DayOfWeek]

select * from @baddata

For the third row, I'm getting a date in September, not August, but I think that the result is right and your sample result is incorrect?

I love it! I kept getting hung up on DateFirst issues with the stuff I was trying. I did accidentally have August instead of September in my example, sorry for the confusion. I corrected it to spare anyone else the same confusion. Thank you very much. — xr280xr, May 11 '11 at 13:57

Adjust Date to match saved day of week

2 Answers2