Updating dates with random time

Question

I have a datetime column, all of them at 12:00 am. Is there a way to update them with random hours, minutes to nearest 1/2 hour while keeping the same date(day) value?

Update Activities set ActivityDate = ....

Answer 1

Here's one option using dateadd:

update Activities
set ActivityDate = DateAdd(minute, 
                           30 * (abs(checksum(NewId())) % 47), ActivityDate);

• SQL Fiddle Demo

And here's a good post about generating random numbers. Using that, multiple by 30 minutes to get to the nearest half hour.

Note, this uses % 47 since there are 1440 minutes in a day -- that divides into 48 potential half hour segments in that same day.