Filling a column with random dates ? 1

[Chance1234]

I have a table which has just had a new column added to it which is a datetime field

no problems there , but i need to test the output of this column in teh rest of the system

At the moment there are several thousands records and what i want to do is populate this new column with random dates from the last year to the current date,



Chance,

F, G + Its official, its even on a organisation chart et all

 

[ca8msm]

[google]sql server populate random date[/google]


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Chance1234]

I need to update a column in an already existing table with a random date, i have searhed google hence why i am asking here



Chance,

F, G + Its official, its even on a organisation chart et all

 

[PerlyGates]

The following will give you a range between 0.0 and 1.0

SELECT RAND()

The following will give you a range between 0.0 and 10000

SELECT RAND() * 10000

The following will add up to 10000 days (~27 years) onto the current date

SELECT GETDATE() + (RAND() * 10000)

To subtract dates you obviously just use a minus instead of the plus.

So this is what I'd do ... (The CASE statement will dicate whether the dates are added or subtracted i.e. past or future)

SELECT CASE WHEN RAND() < .5 THEN GETDATE() - (RAND()*10000) -- past date
            ELSE GETDATE() + (RAND()*10000) -- future date
       END

Putting it all together

DECLARE @myTable TABLE (
    rowId  int ,
	myDate datetime 
)

INSERT INTO @myTable (rowId)
 SELECT 1 UNION ALL
 SELECT 12 UNION ALL
 SELECT 13 UNION ALL
 SELECT 14 UNION ALL
 SELECT 15 UNION ALL
 SELECT 111 UNION ALL
 SELECT 81 UNION ALL
 SELECT 881 UNION ALL
 SELECT 99 UNION ALL
 SELECT 13 UNION ALL
 SELECT 23 UNION ALL
 SELECT 1 UNION ALL
 SELECT 1 UNION ALL
 SELECT 1 

UPDATE @myTable 
   SET myDate = t1_newDate
  FROM ( SELECT CASE WHEN RAND() < .5 
                     THEN GETDATE() - (RAND()*10000) -- past date
                     ELSE GETDATE() + (RAND()*10000) -- future date
                 END   AS t1_newDate
                ,rowId AS t1_rowId 
           FROM @myTable ) t1
 WHERE rowId       = t1_rowId 


SELECT *
  FROM @myTable

Thats just one approach. I'm sure there are easier ones. If you want a greater or lesser range of dates just increase/decrease the 10000 accordingly.

 

[gmmastros]

PerlyGates

When I run your code, all the dates are the same.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PDreyer]

Here is one way

select id, convert(datetime,0) crdate 
into #t1 -- test data
from sysobjects 

update #t1 -- set to a date between 2001 and 2002
set crdate=dateadd(dd,convert(int,rand(convert(int,convert(varbinary(24),right(newid(),12))))
    *(datediff(dd,'20060101',getdate())+1)),'20060101')

select * from #t1 

drop table #t1

 

[PDreyer]

Oops, -- set to a date between 2001 and 2002
should be between 20060101 and current date

 

[gmmastros]

Rand is a weird little function. To use rand effectively, you need to supply it a 'seed' value that is different for each row. The seed value needs to be a tinyint, smallint or int. So... how can we manufacture a random seed to use for the rand function? Well, knowledge of certain functions can help. For example, NewId() returns a random guid for each row. But, how do we convert from guid to int? Checksum of course.

Check it out:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Id [COLOR=blue]int[/color] [COLOR=blue]Identity[/color](1,1))
[COLOR=blue]Declare[/color] @i [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] @i = 0
[COLOR=blue]while[/color] @i < 1000
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Default[/color] [COLOR=blue]Values[/color]
    [COLOR=blue]Set[/color] @i = @i + 1
  [COLOR=blue]End[/color]

[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]Rand[/color](), [COLOR=#FF00FF]Rand[/color](Checksum(NewId()))
[COLOR=blue]From[/color]   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

The reason I posted the google link is because the first post shows the logic of how you can define a random date within a certain date range. Combining this with George's example above would have produced something like this:

[COLOR=green]-- Declarations
[/color][COLOR=blue]declare[/color] @table1 [COLOR=blue]table[/color] (id [COLOR=blue]int[/color], mydate [COLOR=#FF00FF]datetime[/color])
[COLOR=blue]declare[/color] @seqnum [COLOR=blue]int[/color]  

[COLOR=green]-- create some sample data
[/color][COLOR=blue]set[/color] @seqnum = 0  
[COLOR=blue]while[/color] @seqnum <= 100  
 [COLOR=blue]begin[/color]  
     [COLOR=blue]insert[/color] @table1 (id) [COLOR=blue]values[/color] (@seqnum)  
     [COLOR=blue]set[/color] @seqnum = @seqnum + 1  
[COLOR=blue]end[/color]  

[COLOR=green]-- perform your update
[/color][COLOR=blue]update[/color] @table1 [COLOR=blue]set[/color] mydate = [COLOR=#FF00FF]GetDate[/color]() - ((18 * 365) + [COLOR=#FF00FF]Rand[/color](Checksum(NewId()))*(47*365))

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[PerlyGates]

Hi George

>>When I run your code, all the dates are the same.

Is that on 2000??

I've just ran it on 2000 and all the dates are indeed the same. But on 2005 they are all different - I didn't know or would have thought there would have been a difference.

Or is it to do with default settings of the database one runs it on???

 

[gmmastros]

Yes.

I ran that on SQL2000. I didn't know that would make a difference either. I guess Microsoft changed the functionality in SQL2005.

Thanks for pointing that out.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PerlyGates]

No - thankyou.

 

[AlexCuse]

PerlyGates - what service pack level are you on for SQL 2005?

I have service pack 2 installed (9.00.3042), and I get the same date in each row. So if you are using this code for anything, you may need to keep a close eye on it when the time comes to upgrade to a new service pack.

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[PerlyGates]

Thanks folks. What I didn't reveal (because I didn't think it relevant) was that I was running that code on SQL SERVER 2005 Express Edition SP2 (9.00.1399.06).

So if I run the following SELECT on SQL SERVER Express ...

SELECT TOP 5 
       CASE WHEN RAND() < .5 
            THEN RAND() - 1 
            ELSE RAND() + 1 
       END 
  FROM sysobjects

... I will get 5 unique values.

However if I run that same bit of code on SQL SERVER 2000 Enterprise Edition SP1 (8.00.2039) and SQL SERVER 2005 Enterprise Edition SP1 (9.00.3050.00)

All rows will have the same values.

PLEASE NOTE that if I run the following bit of code ...

SELECT TOP 5 
       RAND() - 1 
  FROM sysobjects

... all rows will have the same values REGARDLESS of what database I run the SELECT on.

I would not have expected such behaviour - particularly a difference between Enterprise and Express.

Thanks again for highlighting this.


-PG

 

[ESquared]

CREATE VIEW MyRandView
AS
SELECT rnd = Rand()
GO
CREATE FUNCTION MyRandFunction()
RETURNS float
AS
BEGIN
   RETURN (SELECT rnd FROM MyRandView)
END
GO
UPDATE MyTable SET Column = dbo.MyRandFunction() * 3012153 - 53690

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]