Random Date/Time Test Data Generation

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.