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.