How to update rows with a random date
I have a simple SQL table which has a DateTime column. I would like to update all the rows (>100000 rows) with a random date. Is there a simple way to do this a SQL Query?
Solution 1:
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
Solution 2:
I will complement the answers below,
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
FROM your_table
This generates dates starting from 2000-01-01, and you can change the amount of days in the modulus value, I put 3650 (about 10 years), this approach doesn't overflow.
If you want to update, then
UPDATE your_table
SET your_date_field = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01')
WHERE your_conditions