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