How to Update Rows with a Random Date

How to update rows with a random date

Use this to generate a smalldatetime between 01 Jan 1900 and 06 Jun 2079 (not checked, SQL not installed)

DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

NEWID is better then trying to use RAND: RAND does not generate different values row in a single SELECT or UPDATE (well it didn't in SQL 2000, in case behaviour has changed).

Edit: like this

UPDATE
  table
SET
  datetimecol = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

Edit: changed 65535 to 65530 and added ABS to avoid overflow at upper limit of range

update every row with a different random datetime of a range

With:

strftime('%s', enddate) - strftime('%s', startdate)

you can get the difference in seconds between 2 datetimes.

With:

abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1))

you can get a random integer, greater or equal to 0, that is less than or equal to the difference between the 2 datetimes in seconds.

What you can do is add this random number of seconds to the starting date of your range to create a random datetime within that range:

update tablename 
set datecol = datetime(
  startdate, 
  abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1)) || ' second'
)

datecol is the date column in your table

startdate and enddate are the boundaries of your range.

Update every row with a random datetime between two dates

Use an expression in place of a query:

update my_table 
set date_created= NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days'

Update a series of random dates in a Postgres column

You want an update statement rather than an insert - and presumably, you want different dates on each row (the time portion does not seem relevant, otherwise you would rather use a timestamp than a date).

If your ids are always increasing, you could just them for increment:

update renewal_large
set policy_expires = '2020-07-01'::date + (profile_id::int - 1) * interval '1' day

Otherwise, you can enumerate the rows with row_number(), then use it to compute the target date:

update renewal_large r
set policy_expires = '2020-07-01'::date + (r1.rn - 1) * interval '1' day
from (select id, row_number() over(order by id) rn from renewal_large) r1
where r1.id = r.id

Insert/ Update random date in MySQL

You can get a random integer with this expression:

To obtain a random integer R in the
range i <= R < j, use the expression
FLOOR(i + RAND() * (j - i)). For
example, to obtain a random integer in
the range the range 7 <= R < 12, you
could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand

Use that to generate a random number of days, hours or minutes (depending on the resolution) and add that number to current date.

Full expression would be:

-- Date only
SELECT CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY;

-- Date and time
SELECT CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 14 * 24 * 60 *60) SECOND;

Demo

update multiple rows with random datetime sql server

You could:

UPDATE TBL
    SET [TIME] = DATEADD(MINUTE, ABS(CHECKSUM(NEWID()) % 10) + 1, [TIME])

Not very efficient but presumably this is just for testing.

Updating dates with random time

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.

---

Related Topics