Generating Sequential and Random Date Ranges

--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

/*

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 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

--===== 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