Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create random time on sql query

Posted on 2013-01-13
1
Medium Priority
?
482 Views
Last Modified: 2013-01-13
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
Comment
Question by:rafaelrgl
1 Comment
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 38772204
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question