Random Date/Time Test Data Generation






4.86/5 (4 votes)
Generate random dates (and times if you like) for your test data environment.
Introduction
Many times in software development, much of the challenge to assuring a quality product is the creation of simulated yet real-world test data. One of the big headaches in this endeavor is the creation of applicable dates and times. The SQL Server function and example that follows may relieve that headache a bit.
Preface
Important up front note: Since the SQL Server nondeterministic RAND()
function cannot be used in a user defined function, you must create the following view in the same database that contains the function:
CREATE VIEW [dbo].[RandNum]
AS
SELECT RAND() RandNum
This is a slight cheat, but it gets the job done.
The Function Code
The random date function takes three parameters: Start Date, End Date and a flag to include a random time component if desired (the default is 00:00):
--==============================================================
-- Generate a random date between a supplied start and end date
-- Also include a random time component if desired.
-- Author: Gene Browning
-- Date: 08/25/2016
--
-- Note: This function requires the following view:
-- CREATE VIEW [dbo].[RandNum]
-- AS
-- SELECT RAND() RandNum
--==============================================================
CREATE FUNCTION [dbo].[RandDate] (
@StartDate DATE,
@EndDate DATE,
@IncludeTime BIT = 0
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ReturnDate AS DATETIME
DECLARE @dDiff AS INT
DECLARE @temp AS FLOAT
-- Create the random date based on the start/end date day span
SET @dDiff = DATEDIFF(dd, @StartDate, @EndDate)
SET @dDiff = CONVERT(INT, ROUND(@dDiff * (select RandNum from RandNum), 0))
SET @ReturnDate = DATEADD(dd, @dDiff, @StartDate)
-- If time flag is set, add in a random time component
IF @IncludeTime = 1
BEGIN
SET @ReturnDate = DATEADD(hh, ROUND(23 * (select RandNum from RandNum), 0), @ReturnDate)
SET @ReturnDate = DATEADD(mi, ROUND(59 * (select RandNum from RandNum), 0), @ReturnDate)
SET @ReturnDate = DATEADD(ss, ROUND(59 * (select RandNum from RandNum), 0), @ReturnDate)
SET @ReturnDate = DATEADD(ms, ROUND(1000 * (select RandNum from RandNum), 0), @ReturnDate)
END
RETURN @ReturnDate
END
Simple Example
In this example, a random date with a time component is returned for all of 2014 and the first half of 2015. Note that if you want to return the same random set on each execution, use the @SeedWork
line, one time only, to prime the random number seed. Otherwise, this line may be omitted and a different random set will be generated each time:
DECLARE @SeedWork as float = RAND(1) -- Optional if you want consistent results
SELECT dbo.RandDate('2014-01-01', '2015-06-30', 1) AS 'ReturnDate'
A More Practical Example
The following example uses the function to generate 100 random test Customer Order
rows for customers of IDs 1-1000 with random order dates. The ship date is then randomly updated to be within 0-10 days of the order date:
DECLARE @OrderHeader TABLE (
OrderID INT IDENTITY(1, 1) NOT NULL,
CustId INT,
OrderDate DATE,
ShipDate DATE
)
DECLARE @counter SMALLINT
DECLARE @MaxCustId AS INT
DECLARE @OrderDateLow AS DATE
DECLARE @OrderDateHigh AS DATE
DECLARE @IncludeTime AS BIT
SET @counter = 1
SET @MaxCustId = 1000
SET @MaxCustId = @MaxCustId - 1 -- Adjust for RAND value being zero based
SET @OrderDateLow = '2015-01-01'
SET @OrderDateHigh = '2016-06-30'
SET @IncludeTime = 0
WHILE @counter <= 100
BEGIN
INSERT INTO @OrderHeader (
CustId,
OrderDate
)
VALUES (
CONVERT(INT, ROUND(@MaxCustId * RAND(), 0) + 1),
dbo.RandDate(@OrderDateLow, @OrderDateHigh, @IncludeTime)
)
UPDATE @OrderHeader
SET ShipDate = DATEADD(dd, ROUND(10 * RAND(), 0), OrderDate)
WHERE OrderId = @@IDENTITY
SET @counter = @counter + 1
END
SELECT OrderID,
CustId,
OrderDate,
ShipDate,
DATEDIFF(dd, OrderDate, ShipDate) AS 'DaystoShip'
FROM @OrderHeader
Summary
I sincerely hope that this information comes in handy and please let me know about your improvements, questions and comments.