In this blog I will show how to generate random datetime for a given range of dates. You may be familiar with Rand() function of SQL which is used to generate random numbers at run time. I have used the same rand() fundtion with some more logic with date manipulation functions. Below is the script of the user defined function which will generate random datetime between the range of dates. This function accepts two arguments of datetime type as the input, one is startdate and the other one enddate and returns datetime type data.
CREATE FUNCTION [GenerateRandomDateTime]
DECLARE @RandomDateTime DATETIME
DECLARE @RandomNumber FLOAT
DECLARE @IntervalInDays INT ;
SET @IntervalInDays = CONVERT(INT, @EndDate - @StartDate)
SET @RandomNumber = ( SELECT [RandNumber]
SELECT @RandomDateTime = DATEADD(ss, @RandomNumber * 86400,
@RandomNumber * @IntervalInDays,
CREATE VIEW [RandNumberView]
AS SELECT RAND() AS [RandNumber]
Above you must be seeing that I have used [RandNumberView] view in it. The reason is that we can't use time dependent operator in a function and if I had used rand() simply in the function I would have received this error "Invalid use of side-effecting or time-dependent operator in 'rand' within a function". In the above function I have used the "Rand" logic on both day and time in order to get random datetime. Let's test by creating a sample table and fill it with random data by using this function.
CREATE TABLE [DateTable]
[ID] INT IDENTITY,
--Let's add some data
INSERT INTO [DateTable]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
SELECT * FROM [DateTable]
Above "GO 50" will execute the insert script batch 50 times so above 50 random datetime are inserted in the table. Below is the screenshot of the output which I received after running the above script.
Do let me know your feedback, comments.