Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with:

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

By Ben Nadel on
Tags: Ask Ben, SQL

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.



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader 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.

Reply to this Comment

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

Reply to this Comment

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))

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.