• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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
0
rafaelrgl
Asked:
rafaelrgl
1 Solution
 
Steve WalesSenior 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now