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

sql server add random seconds in date

i have a table like this

Dates

Id int
StartDate datetime
EndDate datetime


what i want is to make a select and add from -5 to 5 minutes to the dates so i tried something like this:

select id, dateadd(second, convert(int, 600*RAND()) - 300, StartDate), dateadd(second, convert(int, 600*RAND()) - 300, EndDate)
from Dates

it worked.. but for all rows the random was the same.. if the random in the first row was for eg. 1min then all other rows had plus 1 min.. i want all the rows to have different rnd values.. is it possible ? i really dont want to use a cursor or something like that
0
eguilherme
Asked:
eguilherme
1 Solution
 
eguilhermeAuthor Commented:
found the solution here:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx#36900


"
Try this in 2005 (or any other SQL):

RAND(CAST(NEWID() AS BINARY(6)))

As long as you provide a seed to the RAND function it becomes non deterministic and evaluates at every row. All that you have to do is make sure that your seed changes so that you get a new number during each call. You don't really have to worry about how random the seed is because the RAND function will take care of the randomization for you.
"

i wont delete so others with the same issue can find it here
0
 
DCMBSCommented:
If Rand is seeded with a row dependant value it will be evaluated for each row,  otherwise it is only evaluated once per statement.

Try something along the lines of

select id, dateadd(second, convert(int, 600*RAND(id)) - 300, StartDate), dateadd(second, convert(int, 600*RAND(id)) - 300, EndDate)
from Dates

Have a look at http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx for further info
0
 
thenelsonCommented:
The problem is a function in the field section of a query is called only once unless a changing variable is passed in the argument of the function. If a positive number is passed to Rand, Rand returns the next Random number, so try:

select id, dateadd(second, convert(int, 600*RAND(Nz(StartDate,1))) - 300, StartDate), dateadd(second, convert(int, 600*RAND(Nz(StartDate,1))) - 300, EndDate)
from Dates
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Can you post some sample set and your expected result?
0
 
Anthony PerkinsCommented:
>>found the solution here:<<
Since you have found the solution, kindly close the question.
0
 
eguilhermeAuthor Commented:
since he posted at the same time i did i suppose i can give him the credits
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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