Solved

Create random time on sql query

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

12 Experts available now in Live!

Get 1:1 Help Now