Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Brian Crescimanno
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Brian Crescimanno ( @bcrescimanno )

Ask Ben: Getting A Random Date From A Date Range In SQL

By on
Tags: ,

How can I select a random date from a date range in SQL server. I basically want to do RandRange() for dates in sql? Does that make sense?

I have to admit, this one totally stumped me at first. I rarely work with random data in SQL. If I need random data I usually just build a query in ColdFusion. I tried looking up random methods for SQL, but it turns out that things like RAND() are only called once per table, NOT per row. I got some great ideas from Jon Galloway over at http://weblogs.asp.net/jgalloway/archive/2004/03/18/92498.aspx. He supplied the way to come up with a random date:

cast(
	cast( getdate() as int )
	-5 * rand( cast( cast( newid() as binary(8) ) as int ) )
	as datetime
	)

I am not exactly sure what the casting of the UUID to a binary(8) does, but I see that it converts to an integer.

After some experimentation, I see that casting UUIDs to binary can result in either a negative or positive number. For a date range, I needed to work with a positive number. Therefor I use ABS() to force a positive outcome. Once we have that, all we need is the date range part. That's where our old friend date-math comes in. You can think of a random date from a date range as adding a random number of days to the start date (>= 0) such that the resultant date does not exceed the end range date.

Once you think about it that way, we have all the pieces we need:

-- First, let's declare the date range. I am declaring this
-- here for the demo, but this could be done anyway you like.
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 = '2006-10-01';
SET @date_to = '2006-10-30';

-- Select random dates.
SELECT
	(
		-- 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 GTE 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
			)
		)
	)

Hope that helps.

Want to use code from this post? Check out the license.

Reader Comments

3 Comments

This works as well.

select DATEADD(second, rand()*86400, DATEADD(d, rand()*(CAST((@date_to - @date_from) AS INT )), @date_from))

86400, being the number of seconds in a day.

Cheers.

1 Comments

Thanks! This script helped me a lot in trying to generate a buncha test data where we're tracking stats based on unique datetimes.

1 Comments

Here's a slightly modified version... selecting a random time during an 8hr period during a particular day. (28800 is the number of seconds in 8 hours.) I send the NEWID() through RAND() to guarantee a unique timestamp for each row.

DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;

SET @date_from = '2009-06-29 08:00:00.000';
SET @date_to = '2009-06-29 16:00:00.000';

SELECT DATEADD(second, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(d, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*(CAST((@date_to - @date_from) AS INT )), @date_from))

1 Comments

Very good code. It worked fine, the only thing that I have to check is the Date Format, as my SQL Server 2005 is in Portuguese I had to change the format to YYYY-DD-MM

Thank you

1 Comments

I did it like that

random date betweem 1-9-2009 to 29-1-2010
(between 0 to 150 days)

DECLARE @RandomNumber float
DECLARE @RandomDayF int
DECLARE @MaxDay int
DECLARE @MinDay int

SET @MaxDay = 150
SET @MinDay = 0

-- random the number of days to add
SELECT @RandomNumber = RAND()

set @randomDayF = ((@MaxDay + 1) - @MinDay) * @RandomNumber + @MinDay

-- add the days
Set @FromDate = (select DATEADD(d, @randomDayF,(SELECT convert(datetime, '1-9-2009',105)))) -- dd-mm-yyyy

--to reduce days
-- @randomDayF = @randomDayF * -1

1 Comments

I used this sample to generate random number from 1 to 1000000000
amazingly it produce 9-digit most of the time

I want it to generate more like 1,2,3,..9-digit

actually I need it in a lottery app
any other suggestion I'll appreciate it

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel