How to update rows with a random date

This question seems quite old but my answer might be usefull to others.

      Update table
      SET Time= DateAdd(d, ROUND(DateDiff(d, '2010-01-01', '2013-12-31') * RAND(CHECKSUM(NEWID())), 0),
      DATEADD(second,CHECKSUM(NEWID())%48000, '2010-01-01'))

This generates a random datetime between a given range.


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

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


I adapted Jhonny's answer above to get dates from 10 years in the past:

SELECT dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate())

Note that this is SQLServer only.

Tags:

Sql

Sql Server