Generating a Random DateTime in a Range in SQL Server

There are lots of answers on Stack Overflow about generating a random date within a range. But what if you wanted a random date, and a random time?

I ended up with this snippet:

-- Change these
DECLARE @date_from AS DATETIME = '2015-05-01'
DECLARE @date_to AS DATETIME = '2015-05-31'

-- Don't change this
DECLARE @days_diff AS INT = cast(@date_to - @date_from AS INT)

SELECT @date_from +
DATEADD(second, ABS(CHECKSUM(newid()) % 60), 0) + -- random seconds
DATEADD(minute, ABS(CHECKSUM(newid()) % 60), 0) + -- random minutes
DATEADD(hour, ABS(CHECKSUM(newid()) % 24), 0) + -- random hours
DATEADD(day, ABS(CHECKSUM(newid()) % @days_diff), 0) -- random days

This gives you granularity up to the second in terms of randomness. (For some reason, simply using DATEADD with a mod of 60 * 60 * 24 * @days_diff results in a loss of hour/minute/second precision.)

If you want an explanation, ABS(CHECKSUM(newid()) is an approximation of a random number generator in SQL, for when you want a different random value per-row (RAND gives you the same value for all rows in the query). By guaranteeing that it’s a positive value, you can modulo it to get a random number in a range.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Tools and tagged . Bookmark the permalink.

Leave a Reply

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