sql server add random seconds in date

Posted on 2009-12-23
Last Modified: 2012-05-08
i have a table like this


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
Question by:eguilherme
    LVL 10

    Author Comment

    found the solution here:

    Try this in 2005 (or any other SQL):


    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
    LVL 9

    Accepted Solution

    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 for further info
    LVL 39

    Expert Comment

    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
    LVL 40

    Expert Comment

    Can you post some sample set and your expected result?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>found the solution here:<<
    Since you have found the solution, kindly close the question.
    LVL 10

    Author Closing Comment

    since he posted at the same time i did i suppose i can give him the credits

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now