Generating million’s of random dates/times as Test data

Hi folks,

Often we play a lot with dates in SQL Server and need test dates for testing purpose.

The following code generates million random dates/times from the first instant of the year 2000 to the last instant of 2009 (ten full years in total) in about 5 seconds.


--===== Do this testing in a nice safe place that everyone has
    USE TempDB
--===== Create and populate a test table with a million dates
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and 
     -- <01/01/2010 non-unique date/times
 SELECT TOP 1000000
        RowNum   = IDENTITY(INT,1,1),
        SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
   INTO dbo.JBMTest
   FROM sys.All_Columns t1,
        sys.All_Columns t2
Why a million dates? For two reasons: 1) I'm a "batch programmer" and million row tables are actually considered 
to be a fairly small table compared to what most batch programmers normally work with and 
2) all of the code runs extremely fast so we need at least that many rows to start seeing the differences. 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s