CODEWITHSUNDEEP
sql-server-2008stored-procedures
Praviin Zurrunge
Praviin Zurrunge

Reputation: 113

I want to generate random date in SQL Server

Can anyone please tell me I want to generate 100 random dates for my table:

create table dates (dt date)

This code is ok I want to put below code in loop so that it will generate 100 random dates

SELECT
    DATEADD(second, (rand() * 60 + 1), 
    DATEADD(minute, (rand() * 60 + 1),
    DATEADD(day, (rand() * 365 + 1), 
    DATEADD(year, -1, getdate())

or any other simple code to generate random date give me if you can.

Thanks

praviin

Upvotes: 0

Views: 1507

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is an example:

select top 100 dateadd(ss, cast(abs(checksum(newid())) as int), '19000101') as rndDate
from master..spt_values

Fiddle http://sqlfiddle.com/#!3/9eecb7/6049

For predefined range:

with cte as(select row_number() over(order by number) % 366 r 
            from master..spt_values)
select top 100 dateadd(dd, r, '20141231') from cte order by newid()

r will contain values from 1 to 366. Adding to '20141231' will give values from desired range.

Upvotes: 1

Steven Hewitt
Steven Hewitt

Reputation: 302

What you want is really only possible via stored procedures. I'd recommend studying the MySQL compound-statement syntax, especially loop syntax.

You'd have to put in your own date generation code, but code within this loop should run 10 times when called:

CREATE PROCEDURE iterateTen()
BEGIN
  DECLARE p1 INT DEFAULT 0;
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
     ITERATE label1;
    END IF;
   LEAVE label1;
  END LOOP label1;
END;

Upvotes: 0

Related Questions