SQL Job for updating datetime in a table

Question

I have a table with demo data that I want to maintain. The data in the table contains datetime and I would like to create a job to run nightly to update the datetime so that the datetimes are never older than 1 week old and never a future datetime then the current datetime. I would like to maintain the order of the messages when ordering by datetime so that when I update that column one row never leap frogs over the other. I can't come up with a formula to do this without some rows eventually leapfrogging when ordering by datetime. Any suggestions?

My issue is with the formula. Adding one day to all will still make it a future date for the last message if dated Tuesday at 5pm and I add 1 day it will be then wednesday at 5 pm when demoing on Wednesday morning. Adding one day with exception of the most recent may leapfrog the next closest day causing the most recent message to fall back in the order.

Answer 1

If you currently have a number of dates spanning more than one week, this will not be possible by simply adding time to each date. If the time frame between the records doesn't matter, only the order, min, and max, I would sort the dates with a row_number function and add that sort number in seconds to 1 week ago:

update t
    set t.YourDateColumn = u.NewDate
    from YourTable t
        inner join  (select ID,
                        dateadd(second,
                            row_number() over(order by YourDateColumn),
                            dateadd(week,-1,getdate())) NewDate
                     from YourTable
                     ) u
            on u.ID = t.ID

This will work if you have up to 604.8K records. If you have more than that, you'll have to add milliseconds. That will work for up to 604.8M records.