Solved

Create random time on sql query

Posted on 2013-01-13
1
410 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 22

Accepted Solution

by:
Steve Wales earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now