7

I am trying to generate a large data set which includes time datatype in SQL Server 2008. I already have some non-time data in a table so I'd like to keep the entire process in T-SQL and use an insert-into-select to get the partial data from one table and insert it into the next along with some generated data including the time.

I'd like a way to generate random time(7)s between two points, say a random time between 8:00 and 9:00. I've found some pre-2008 post but nothing that addresses SQL Server 2008's time type.

potashin
  • 44,205
  • 11
  • 83
  • 107
Brandon
  • 207
  • 3
  • 7

2 Answers2

9

There are 86,400,000 milliseconds in a day, so you can get a random time value by doing this:

select dateadd(millisecond, cast(86400000 * RAND() as int), convert(time, '00:00'))

For your example where you want times between 8:00 and 9:00, there are 3,600,000 milliseconds in an hour, so modify the query like this.

select dateadd(millisecond, cast(3600000 * RAND() as int), convert(time, '08:00'))

In order to put in into your new table, you might either do a T-SQL loop with updates (s...l...o...w...), or do a SELECT INTO from your original table into a new table.

mattmc3
  • 17,595
  • 7
  • 83
  • 103
  • This will create one random value but won't be able to be used to create multiple random values in a `SELECT` statement. – Martin Smith Dec 06 '10 at 00:10
  • Yes, you'll have to put your inserts into a T-SQL loop. Do you need me to change my post to include the syntax? – mattmc3 Dec 06 '10 at 00:12
  • I'm not the OP! I was just pointing out a benefit of `newid()` over `rand()` – Martin Smith Dec 06 '10 at 00:14
  • Both are good answers. I like this one because it's short although the RAND() thing is an issue that has to be worked around. – Brandon Dec 11 '10 at 17:46
6

To generate 100 rows of test data you can use the below.

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b)
SELECT TOP 100 CAST(DATEADD(SECOND,ABS(CHECKSUM(NEWID()))%3600,'08:00') AS TIME)
FROM E32
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Perhaps it's good to point out that the WITH Exx generates dummy rows to do a select over and is not required to get a random value. It's a pretty smart and compact way but it took me some time to get it. – Frank de Groot - Schouten Sep 25 '13 at 09:47
  • How about replacing the WITH Exx with `WITH X(X) AS (SELECT 1 UNION ALL SELECT 1 FROM X) SELECT ... OPTION (MAXRECURSION 100)`? Apparently it is limited to 32k rows but that might not be a problem. – Frank de Groot - Schouten Sep 25 '13 at 10:06
  • @FrankdeGroot-Schouten - [Not as efficient](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) – Martin Smith Sep 25 '13 at 12:39