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:

Next this is a worked out version of selecting random dates based on a range.

Here is a full script to make this work:

As usual, writing something semi-complicated in tSQL is a giant pain in the ass, but expected…

Leave a Reply

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