I had a very interesting experience recently trying to figure out how to select a random date between a date range using tSQL, it was a lot of trial and error, but ultimately this is what I came up with. It is only as random as the RAND() function will allow it to be unfortunately.
Read up on the function here: MSDN tSQL RAND()
WARNING When using the RAND() function with a specified seed for multiple results/rows in a select statement, unfortunately the RAND() function with a specified seed will yield the same result each time. To get a different result for each row you must unfortunately use a loop (while, cursor, etc…). I think this is highly inconvenient, but it is the truth. Examples: SELECT (RAND(100)*100 – RAND(100)*100) will yield zero, where as SELECT (RAND() – RAND()) will yield a random number.
Here are some of the basics first about how to work with this stuff:
/* Some of the basics first */ -- Get the int value for today's date Select cast(getdate() as int) -- 40913 Select cast(cast('1 Jan 2010' as datetime) as int) -- 40177
Next this is a worked out version of selecting random dates based on a range.
/* Selecting a random date based on a range */ -- Get the High date int value Select cast(cast('1/6/2012' as datetime) as int) -- 40912 -- Get the Low date int value Select cast(cast('1/6/2010' as datetime) as int) -- 40182 -- Compute the Difference Select 40912 - 40182 -- 730 -- Get a number that is not higher than the higher limit and not lower than the lower limit -- High - Random * Difference SELECT (40912 - RAND()*730) -- Random limited multiplier SELECT cast(cast((40912 - RAND()*730) as int) as datetime) -- Selecting random date based on limited multiplier
Here is a full script to make this work:
declare @intDateDiff int, -- The numerical difference between dates @intDtmHigh int, -- The numerical representation of the High Date @dtmLow datetime, -- The Low Date @dtmHigh datetime; -- The High Date -- I always initialize my variables, I have run into avoidable hard to trace problems when I haven't in the past set @intDtmHigh = 0; set @intDateDiff = 0; set @dtmHigh = GETDATE(); -- I just happened to use GETDATE(), you can use whatever you want set @dtmLow = DATEADD(YEAR, -2, GETDATE()); -- I put a negative 2 to indicate a two year spread, change to whatever you want -- Get the High date int value Select @intDtmHigh = cast(@dtmHigh as int); -- Subtract the Low date int value from the High Date int value Select @intDateDiff = (@intDtmHigh - cast(@dtmLow as int)); -- Select the random date Select cast(cast((@intDtmHigh - RAND()*@intDateDiff) as int) as datetime);
As usual, writing something semi-complicated in tSQL is a giant pain in the ass, but expected…