How to update rows with a random date

sql

performance

best-practices

database

byAlex Kataev·Dec 1, 2024

Need speedy results? Generate random dates using RANDOM() or RAND(). For PostgreSQL, use:

UPDATE your_table SET your_column = CURRENT_DATE - (RANDOM() * N)::int;

And for MySQL, the code is:

UPDATE your_table SET your_column = CURDATE() - INTERVAL FLOOR(RAND() * N) DAY;

Just replace your_table, your_column, and N to suit your scenario. Watch your column magically fill with random dates within the past N days.

Concocting randomness in SQL Server

Ever played with NEWID() in SQL Server? It's like RAND(), but with unique results per row. Use CHECKSUM(NEWID()) with ABS() to get a positive value and dodge the nasty overflow. Put together DATEADD, CHECKSUM, and NEWID() to have fun with random dates:

-- So random, it could predict your horoscope!
UPDATE your_table
SET your_date_column = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % N, '2021-01-01')
WHERE your_condition;

N being the total number of days. Bet you didn't see that coming!

Nailing specific date criteria

Tailoring date ranges

Need a date, say, from the past 10 years? Here's how:

-- Date generator gone time traveling!
UPDATE your_table
SET your_date_column = DATEADD(YEAR, - (ABS(CHECKSUM(NEWID())) % 10), GETDATE())
WHERE your_condition;

Your column will get updated with dates as random as lotto numbers from the last decade!

Efficiency: Do try this at home!

Beware, large tables!

Got massive tables? Brace for performance impact. NEWID() generates new values per row and might exhaust your server before your coffee gets cold. Pre-generating random numbers could get you optimized glory.

Powering your batch updates

Batch updates are like powerlifting: they need strategic strength. Power up by:

Storing random numbers in a temporary table (your personal gym!).
Calculating random numbers externally (pre-workout routine).
Indexing your condition column for speedy WHERE clause fetches (lightning-fast reflexes).

Don't let `RAND()` fool you

RAND() is a trickster, producing the same value in a batch. Use NEWID() instead—and retain your sanity.

Overflow: Not just for plumbers

Unwanted negatives? Get ABS() to the rescue! It prevents overflow errors with DATEADD—like a superhero plumber fixing leaky pipes.

Modulus: Your randomizer toolkit

The modulus operator (%) is like your trusty wrench, ensuring your random date fits in the expected range.

Precision on demand

For precise ranges within specific dates, pair up ROUND and DATEDIFF:

-- Because who doesn't like precision?
UPDATE your_table
SET your_date_column = DATEADD(DAY, ROUND(DATEDIFF(DAY, @StartDate, @EndDate) * RAND(), 0), @StartDate)
WHERE your_conditions;