Generate random SQL Server 2008 time test data

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开发者_如何学Python 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.

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.

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