How to update rows with a random date

Question

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?

Answer 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

Answer 2

Using the code below you can get a random integer between @Min (1) and @Max (365), then using the dateadd funection you can create random dates in the last year.

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
GO

CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS int
AS
 BEGIN
 RETURN round(@Min + (select RandNumber from vRandNumber) * (@Max-@Min),0)
 END
GO

Update table1
set theDate = dateadd(d,0-dbo.RandNumber(1,365),getdate())

Answer 3

you can try getting a random number (positive or negative) then adding that number to a date (possibly system date).

For example (I don't have access to sqlserver right now so I could not verify syntax)

DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary(4))) * 365.25 * 90), 0)

Answer 4

The following code will fill the StartDate column of the FiscalYear table with random dates between two given dates:

-- First, let's declare the date range.
DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;

-- Set the start and date dates. In this case, we are using
-- the month of october, 2006.
SET @date_from = '1985-10-14';
SET @date_to = '2009-04-27';

UPDATE FiscalYear SET StartDate =  
(
    -- Remember, we want to add a random number to the
    -- start date. In SQL we can add days (as integers)
    -- to a date to increase the actually date/time
    -- object value.
    @date_from +
    (
     -- This will force our random number to be >= 0.
     ABS
     (
      -- This will give us a HUGE random number that
      -- might be negative or positive.
      CAST(CAST(NewID() AS BINARY(8)) AS INT)
     )

     -- Our random number might be HUGE. We can't have
     -- exceed the date range that we are given.
     -- Therefore, we have to take the modulus of the
     -- date range difference. This will give us between
     -- zero and one less than the date range.
     %

     -- To get the number of days in the date range, we
     -- can simply substrate the start date from the
     -- end date. At this point though, we have to cast
     -- to INT as SQL will not make any automatic
     -- conversions for us.
     CAST((@date_to - @date_from) AS INT)
    )
)

Answer 5

I will complement the answers below,

SELECT DATEADD(DAY, (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, (CHECKSUM(NEWID()) % 3650), '2000-01-01')
WHERE your_conditions