There are lots of answers on Stack Overflow about generating a random date within a range. But what if you wanted a random date, and a random time?
I ended up with this snippet:
-- Change these
DECLARE @date_from AS DATETIME = '2015-05-01'
DECLARE @date_to AS DATETIME = '2015-05-31'
-- Don't change this
DECLARE @days_diff AS INT = cast(@date_to - @date_from AS INT)
SELECT @date_from +
DATEADD(second, ABS(CHECKSUM(newid()) % 60), 0) + -- random seconds
DATEADD(minute, ABS(CHECKSUM(newid()) % 60), 0) + -- random minutes
DATEADD(hour, ABS(CHECKSUM(newid()) % 24), 0) + -- random hours
DATEADD(day, ABS(CHECKSUM(newid()) % @days_diff), 0) -- random days
This gives you granularity up to the second in terms of randomness. (For some reason, simply using
DATEADD with a mod of
60 * 60 * 24 * @days_diff results in a loss of hour/minute/second precision.)
If you want an explanation,
ABS(CHECKSUM(newid()) is an approximation of a random number generator in SQL, for when you want a different random value per-row (
RAND gives you the same value for all rows in the query). By guaranteeing that it’s a positive value, you can modulo it to get a random number in a range.