Generate random SQL Server 2008 time test data

Question

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.

Answer 1

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.