Loading ...

SQL: Generate random datetime for a given range | CodeAsp.Net

SQL: Generate random datetime for a given range

 /5
0 (0votes)

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]
    (
      @StartDate DateTime,
      @EndDate DATETIME 
    )
RETURNS DATETIME
AS BEGIN
  
	--DECLARE VARIABLES
    DECLARE @RandomDateTime DATETIME
    DECLARE @RandomNumber FLOAT
    DECLARE @IntervalInDays INT ;
    
    SET @IntervalInDays = CONVERT(INT, @EndDate - @StartDate)
    SET @RandomNumber = ( SELECT    [RandNumber]
                          FROM      [RandNumberView]
                        )
                        
    SELECT  @RandomDateTime = DATEADD(ss, @RandomNumber * 86400,
                                      DATEADD(dd,
                                              @RandomNumber * @IntervalInDays,
                                              @StartDate))

	
    RETURN @RandomDateTime

  
   END
GO

CREATE VIEW [RandNumberView]
AS  SELECT  RAND() AS [RandNumber]

GO

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.

GO
CREATE TABLE [DateTable] 
    (
      [ID] INT IDENTITY,
      [Date] DATETIME
    )
--Let's add some data
GO
INSERT  INTO [DateTable]
SELECT DBO.[GenerateRandomDateTime]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
GO 50

SELECT * FROM [DateTable]
GO

 

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.

Comments (no comments yet)

Top Posts