Date and Time - Generating Sequential and Random Date Ranges

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        2 May 2012

Description

This is quite a lengthy set of examples on how to generate both sequential and random sets of dates or date/times.
Each example has an explanation within the code.
To summarize, the following techniques are covered:
  • Generating Sequential Whole Dates - with variations in the sequencing interval (day, month, Friday etc)
  • Generating Sequential Dates with Times
  • Generating Random Dates
  • Generating Random Dates with Times
NOTE:  Some of these examples could possibly be easily implemented utilizing the master calendar table.  However, if the use is small-scale or occasional, then these techniques will likely be more practical.

Code

Generating Sequential Whole Dates:

--Generating Sequential Whole Dates

DECLARE @StartDate DATETIME, --Inclusive
        
@EndDate   DATETIME, --Exclusive
        
@Days      INT;

SELECT @StartDate = '1900', --Inclusive
        
@EndDate   = '1901', --Exclusive
        
@Days      = DATEDIFF(dd,@StartDate,@EndDate);

SELECT TOP (@Days)
        
TheDate = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
  
FROM sys.all_columns ac1
  
CROSS JOIN sys.all_columns ac2


/*
This technique isn't limited to just generating days. You can generate weeks, months, years, etc.
For example, to generate the first of the month for every month in the 21st century, you simply need to change
your calculations from "day" based to "month" based. The rest of the code is essentially identical.
*/

DECLARE @StartDate DATETIME, --Inclusive
        
@EndDate   DATETIME, --Exclusive
        
@Months      INT

SELECT
@StartDate = '2000', --Inclusive
        
@EndDate   = '2100', --Exclusive
        
@Months    = DATEDIFF(mm,@StartDate,@EndDate)

SELECT TOP (@Months)
        
TheMonth = DATEADD(mm,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
  
FROM sys.all_columns ac1
  
CROSS JOIN sys.all_columns ac2



/*
If you wanted something a bit more esoteric, such as finding all of the
Fridays in the year 2012, you simply need to change the point of reference again.
This time, we'll change it to a period of 7 days and use the first Friday in 2012
(automatic calculation of this date is beyond the scope of this article) as a StartDate.
*/

DECLARE @StartDate DATETIME, --Inclusive
        
@EndDate   DATETIME, --Exclusive
        
@Weeks     INT

SELECT
@StartDate = '2012-01-06', --Inclusive
        
@EndDate   = '2013-01-06', --Exclusive
        
@Weeks     = DATEDIFF(dd,@StartDate,@EndDate)/7 --Don't use "WK" here

SELECT TOP (@Weeks)
        
Fridays = DATEADD(wk,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
  
FROM sys.all_columns ac1
  
CROSS JOIN sys.all_columns ac2

/*
Note that "weeks" is a strange creature because there aren't precisely
52 weeks in any given year. In order to include the proper EndDate for other years,
you may have to add a couple of more days to the @EndDate variable depending
on what your needs are.
*/

 
 
  • Generating Sequential Dates with Times:

  • --Generating Sequential Dates with Times

    /*
    Another name for this subsection might be "How to generate sequential time periods".
    We've already seen how to use Division to create "periods" consisting of more
    than one day. What if we want to generate sequential periods for, say, every 4 hours
    for the month of January in 2012? The use of just a little more complicated
    math will serve us well but do notice that the basic code hasn't changed much.
    */

    DECLARE @StartDate DATETIME, --Inclusive
            
    @EndDate   DATETIME, --Exclusive
            
    @Periods   INT

    SELECT
    @StartDate = 'Jan 2012', --Inclusive
            
    @EndDate   = 'Feb 2012', --Exclusive
            
    @Periods   = DATEDIFF(dd,@StartDate,@EndDate)*(24/4) --24 hours in a day, every 4 hours

    SELECT TOP (@Periods)
            
    Every4Hours = DATEADD(hh,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))*4-4,@StartDate)
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2


    /*
    Creating dates and times every 6 seconds for the month of Jan 2012 (for example) only
    requires a shift in the type of period and a little difference in the math.
    You just need to change the math to correctly define the number of periods, change
    the period type in the DATEADD, and change the multiplication and subtraction to
    represent the number of periods. Other than those minor changes, the code continues
    to be essentially the same as in all the other examples for sequential dates with
    times we've seen so far.
    */

    DECLARE @StartDate DATETIME, --Inclusive
            
    @EndDate   DATETIME, --Exclusive
            
    @Periods   INT

    SELECT
    @StartDate = 'Jan 2012', --Inclusive
            
    @EndDate   = 'Feb 2012', --Exclusive
            
    @Periods   = DATEDIFF(dd,@StartDate,@EndDate)*(24*3600/6) --24 hours in a day, 3600 seconds per hour, every 6 seconds

    SELECT TOP (@Periods)
            
    Every6Seconds = DATEADD(ss,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))*6-6,@StartDate)
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2


     
  • Generating Random Dates:

  • --Generating Random Dates and Dates with Times

    /*
    Rather than sequential dates and times, generating random dates and dates with times
    is probably what most people would be after for test data. For example,
    creating data for transactions in multiple bank accounts would require random dates
    (possibly with times) to simulate the unpredictable nature of when people
    wrote checks or hit up their ATM for some fast cash.
    */

    --Generating Random "Whole" Dates

    --===== Declare some obviously named variables
    DECLARE @NumberOfRows INT,
            
    @StartValue   INT,
            
    @EndValue     INT,
            
    @Range        INT

    --===== Preset the variables to known values
    SELECT @NumberOfRows = 1000000,
            
    @StartValue   = 400,
            
    @EndValue     = 500,
            
    @Range        = @EndValue - @StartValue + 1

    --===== Create "random constrained" integers within
         -- the parameters identified in the variables above.
    SELECT TOP (@NumberOfRows)
            
    SomeRandomInteger =  ABS(CHECKSUM(NEWID())) % @Range + @StartValue
      
    INTO #SomeTestTable
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2


    /*
    To demonstrate how to use the random integer generator to generate random dates,
    let's say that we want to generate a million random dates falling in the 10 year
    period of 2010 through 2019. The first thing we need to do is to change the
    variables a bit. We need to change a couple of the variables from the INT
    data-type to DATETIME data-type. While we're at it, we'll also change the
    names of the variables to be more appropriate for the problem at hand.
    */

    --===== Declare some obviously named variables
    DECLARE @NumberOfRows INT,
            
    @StartDate    DATETIME,
            
    @EndDate      DATETIME,
            
    @Days         INT --This is still the "range"

    /*
    Next, we need to preset those variables as we did when we generated sequential dates
    to represent our problem of generating random dates for the years 2010 through 2019.
    */

    --===== Preset the variables to known values
    SELECT @NumberOfRows = 1000000,
            
    @StartDate    = '2010', --Inclusive
            
    @EndDate      = '2020', --Exclusive
            
    @Days         = DATEDIFF(dd,@StartDate,@EndDate)


    /*
    Last but not least, we again need to do like we did during the generation of
    sequential dates. We need to add whole numbers to the StartDate except, in
    this case, we not going to use sequential numbers generated by the likes of
    ROW_NUMBER. Instead, we're going to use our random integer formula.
    */

    --===== Create "random constrained whole dates" within
         -- the parameters identified in the variables above.
    SELECT TOP (@NumberOfRows)
            
    SomeRandomDate = ABS(CHECKSUM(NEWID())) % @Days + @StartDate
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2

    /*
    As you can clearly see, not much has changed from the code that was used
    to generate simple random integers. The random integer formula is the same.
    @Days is stage a "range". We're still adding the start value (a date in this case)
    to the integer. Because the start value of @StartDate is a DATETIME, the
    result of the whole formula is converted to a DATETIME. Because we used only
    integers and we started with a "whole" date, we've generated only whole dates.
    */

    --The following code is all the previous snippets put together with some additions
    --to save the dates in a temp table.

    --===== Conditionally drop the test table to make reruns easier.
        
    IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL
            
    DROP TABLE #SomeTestTable

    --===== Declare some obviously named variables
    DECLARE @NumberOfRows INT,
            
    @StartDate    DATETIME,
            
    @EndDate      DATETIME,
            
    @Days         INT --This is still the "range"

    --===== Preset the variables to known values
    SELECT @NumberOfRows = 1000000,
            
    @StartDate    = '2010', --Inclusive
            
    @EndDate      = '2020', --Exclusive
            
    @Days         = DATEDDIFF(dd,@StartDate,@EndDate)

    --===== Create "random constrained" integers within
         -- the parameters identified in the variables above.
    SELECT TOP (@NumberOfRows)
            
    SomeRandomDate =  ABS(CHECKSUM(NEWID())) % @Days + @StartDate
      
    INTO #SomeTestTable
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2


    /*
    Microsoft introduced the DATE data-type in SQL Server 2008. It is truly
    a "whole" date data-type because it literally has no time element to it.
    There's just one little problem with it. Somewhere along the line,
    they either got "religious" about dates or they didn't want to spend
    the extra bit of development time it would have taken to allow it to be used
    with conventional addition and subtraction. What that means is that
    the previous code example won't work if the @StartDate or @EndDate
    variables are of the DATE data-type.
    */


     
  • Generating Random Dates with Times:

  • --Generating Random Dates with Times

    --===== Conditionally drop the test table to make reruns easier.
        
    IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL
            
    DROP TABLE #SomeTestTable

    --===== Declare some obviously named variables
    DECLARE @NumberOfRows INT,
            
    @StartDate    DATETIME,
            
    @EndDate      DATETIME,
            
    @Days         INT --This is still the "range"

    --===== Preset the variables to known values
    SELECT @NumberOfRows = 1000000,
            
    @StartDate    = '2010', --Inclusive
            
    @EndDate      = '2020', --Exclusive
            
    @Days         = DATEDIFF(dd,@StartDate,@EndDate)

    --===== Create the test table with "random constrained" integers and floats
         -- within the parameters identified in the variables above.
    SELECT TOP (@NumberOfRows)
            
    SomeRandomDateTime = RAND(CHECKSUM(NEWID())) * @Days + @StartDate
      
    INTO #SomeTestTable
      
    FROM sys.all_columns ac1
      
    CROSS JOIN sys.all_columns ac2

    --Here's the code that checks the extent of the random data.  
    --===== Show the extent of the random whole dates
    SELECT MinDateTime   = MIN(SomeRandomDateTime),
            
    MaxDateTime   = MAX(SomeRandomDateTime),
            
    DistinctDates = COUNT(DISTINCT SomeRandomDateTime),
            
    Rows = COUNT(*)
      
    FROM #SomeTestTable

    --===== Show ten rows of the table
    SELECT TOP 10 *
      
    FROM #SomeTestTable



    ċ
    Generate Sequential and Random date sets.sql
    (10k)
    Andy Hughes,
    Jun 26, 2012, 6:36 AM
    Comments