Select a random date within specific range

Question

How can I select a random date within a specific inclusive range, let's say '1950-01-01' and '1999-12-31' with SQL Server?

Answer 1

This will give you 1000 rows of data to insert.

DECLARE @D1 DATE = '19500101'
DECLARE @D2 DATE = '19991231'

   ;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
   SELECT TOP 1000
    DATEADD(DAY,ABS(CHECKSUM(NEWID())) % (1+DATEDIFF(DAY,@D1,@D2)),@D1)
   FROM  cteTally

NB: This answer originally used ABS(CAST(CRYPT_GEN_RANDOM(4) AS INT)) to generate the random numbers. Unlike RAND() which is only evaluated once per statement this is evaluated once per row so would work.

However it seems that the Query Optimiser does not realise this and treats it as a constant. For the purposes of generating random data this probably won't matter (unless you are populating a column constrained by a foreign key)

but I just tested the alternative ABS(CHECKSUM(NEWID())) to see if there was any performance benefit of one over the other.

Typical speeds to generate 1,000,000 rows using the numbers table above and select the MAX value (to avoid overhead of returning all these rows to the client)

ABS(CAST(CRYPT_GEN_RANDOM(4) AS INT))
/*CPU time = 4180 ms,  elapsed time = 4395 ms.*/

ABS(CHECKSUM(NEWID()))
/*CPU time = 953 ms,  elapsed time = 1163 ms.*/

(SELECT 1) /*A constant value just to get a baseline*/
/*CPU time = 499 ms,  elapsed time = 457 ms.*/

So unless you need a cryptographically secure PRNG it's probably best avoided!

Answer 2

select DateAdd(d, ROUND(DateDiff(d, '1950-01-01', '1999-12-31') * RAND(), 0), '1950-01-01')

EDIT

If this is to be executed as part of a statement that returns multiple rows or as part of update, the RAND() would return single value for the whole resultset. For that case RAND(CHECKSUM(NEWID())) can be used.

select DateAdd(d, ROUND(DateDiff(d, '1950-01-01', '1999-12-31') * RAND(), 0), '1950-01-01'),
       DateAdd(d, ROUND(DateDiff(d, '1950-01-01', '1999-12-31') * RAND(CHECKSUM(NEWID())), 0), '1950-01-01')
from master..spt_values where type = 'P'