[SQL Server] – Generate Random Datetime within a range

One of these days I had the need to obtain a random datetime inside a specified range, on SQL Server 2008.

After a few minutes I came up with this script:



DECLARE @DateFrom DATETime = '2012-01-01'
DECLARE @DateTo DATeTime = '2012-06-30'
DECLARE @DaysRandom Int= 0
DECLARE @MillisRandom Int=0

--get random number of days

select @DaysRandom= DATEDIFF(day,@DateFrom,@DateTo)
SELECT @DaysRandom = ROUND(((@DaysRandom -1) * RAND()), 0)

--get random millis
SELECT @MillisRandom = ROUND(((99999999) * RAND()), 0)

SELECT @DateTo = DATEADD(day, @DaysRandom, @DateTo)
SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo)
SELECT @DateTo

Hope it’s useful.

One thought on “[SQL Server] – Generate Random Datetime within a range”

  1. Line 14: SELECT @DateTo = DATEADD(day, @DaysRandom, @DateTo)

    should be

    SELECT @DateTo = DATEADD(day, @DaysRandom, @DateFrom)

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>