How do I make dates in a sample table more recent?

I have a table with about 5,000 records in a ‘sample’ database which has really old data i.e. the ‘Date’ field in this table has records from 2003. This certainly looks ‘old’ in product demonstrations to prospective customers. I want to make make these dates more recent, yet spread them over a month.

The following is what I came up with to update the dates to be more recent. This is one solution I could think of. I’m fishing for other/simple/more elegant solutions from this community.

Declare @Decrement float 
Set @Decrement = 0.0;
With Cte As
(
    SELECT top 9999 * FROM <TableName> Order by ID Desc
)
Update Cte 
Set <MyDateField> = getdate() - @Decrement
  , @Decrement = @Decrement + 0.006

Thank you.

Answer

You could just add a set number of years to all the records with DATEADD(), that keeps them all with the same relevant dates to each other. You could get finer grain by adjusting months also.

With Cte As
(
    SELECT top 9999 * FROM <TableName> Order by ID Desc
)
Update Cte 
SET <MyDateField> = DATEADD(YEAR, -2, <MyDateField>);

Advertisement

Answer