Skip to content
Advertisement

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?

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement