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.