Database Maintenance - Previous Thursday, Current Week, Last day of week.

We keep bumping into some bad field values from the old database.  These usually aren't an issue in our new database but since we use the old one for some stuff it is important to keep it clean throughout.  Each one of our records has 3 fields which correlate it to a specific publication of our newsletter.  The first day of our publishing week, the week, and the last day of our publishing week.  Some of our records were missing these values and I went in search of finding a way to bulk update.  The biggest challenge was getting the beginning day of the week. We work on a Thursday - Wednesday week.  The reasons for this are another blog post.  Anyway to find the previous thursday of any week I used the following:

update table
set beg =
    CASE
        WHEN DATEPART(dw,date) in (5,6,7) THEN dateadd(dd,5-DATEPART(dw,date),date)
        ELSE  dateadd(dd,1-(DATEPART(dw,date)+3),date)
    END
    WHERE beg is null

Basically, what happens is that it checks the day of the week and if it is 5,6, or 7, (that means Thursd, Fri, or Saturday) then it substracts 5 - that number.  So if thursday would be 5-5 = 0 and datadd 0 would stay thursday(5). otherwise take the day and subtract from 1.  This will always be a negative number which works with the datadd to get you back to the previous thursday.  So, if it is a tuesday, that would be a 3. So 3+3 =6 and 1-6 = -5, so Tuesday - 5 days = mon,sun,sat,fri,thurs TADA! back to the previous thursday.

If there be an easier way, let me know.


I mentioned that I also had to get the end date and the week.

For the end date I simply updated the end = dateadd(dd,6,beg) where end is null

Don't get tricked into thinking that is a 7, it is a 6. That's always been a problem for me since there are 7 days in the week. but you add by 6 to get to where you need to be, I promise.

The week was simply a datepart(ww,end)-1.  Why -1, just how it has always been for us.  Calendar week 1 is sometimes very short, I think even 1 day if it is a saturday or something.  Go learn about that, I just know for us we area always at sql(week) - 1 for our current week.