Create random time on sql query

Hi, i want to create an update query that updates the filed nexttime with some random hour, minutes and second. so lets say:

I will execute the query now and today is 2013-01-10, i want something like this


update tb_test set nexttime = rondomdatetime()

where randomdatetime get the next day but random hour, minutes and second between 8am and 10pm,  something like this: 2013-01-11  9:20:15
LVL 1
rafaelrglAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Start with the calculation here: http://stackoverflow.com/questions/4362048/generate-random-sql-server-2008-time-test-data

declare @tomorrow datetime;
-- Get tomorrow's date
set @tomorrow = dateadd(dd, 1, getdate())
-- Truncate the time back to midnight at the start of the day
set @tomorrow = dateadd(dd, datediff(dd, 0, @tomorrow)+0, 0)
-- Add 8 hours to make it 8 AM tomorrow
set @tomorrow = dateadd(hh, 8, @tomorrow)
-- 50400 seconds in 14 hours (window is 8AM to 10 PM
select dateadd(second, cast(50400 * RAND() as int), @tomorrow)
go

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.