Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Random Date Generator

Author  Topic 

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2006-05-11 : 07:42:05
I need to generate some random data - 1000 people and their dates of birth (I want people aged 1 day to 90 years).
Random names were easy from lists of firstnames and surnames.
Random dates seem to be much harder. The only way that I have been able to do it is by generating random years, concatenated with random months, and random days. All converted to the DATETIME datatype.
This is really messy because I need to account for the correct number of days per month so that I don't generate nonsense dates e.g. 31/02/1970 (No apologies for using the CORRECT (UK) date format;-))
Is there anything I can do that utilises GETDATE()?
Ideas? ...better still a ready-made function/stored proc!
Cheers,
Sam

Sorry if this topic has already been covered, but I couldn't find it when I searched the forums.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 07:57:14
[code]
select DOB, datediff(year, DOB, getdate())
from
(
select dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate()) as DOB
) d
[/code]


KH

Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-05-11 : 08:00:16
How does

DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary(4))) * 365.25 * 90), 0)

grab you?
I wouldn't be keen on the completely flat distribution of dates, myself, but it's probably good enough for most demo purposes.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-11 : 08:46:11
This will generate as many random dates as you want.

Function F_TABLE_NUMBER_RANGE is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


select
[DATE] =
-- Get random dates in last 90 year starting from today
dateadd(dd,-rnd,dateadd(dd,datediff(dd,0,getdate()),0))
from
(
select
rnd =
-- Get random integer
abs(convert(int,convert(varbinary(20),newid())))%
-- Modulus by number of days in last 90 years
-- to get a date offset from present
datediff(dd,dateadd(yy,-90,getdate()),getdate())
from
-- Function in script library forum
dbo.F_TABLE_NUMBER_RANGE(1,1000)
) a



CODO ERGO SUM
Go to Top of Page

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2006-05-11 : 09:02:57
Thanks to you all. Just what I needed.
I agree that the distribution of dates is probably unrealistic but this is fine for my purposes.
Most importantly....MUCH less code than I had written.
Cheers,
Sam
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-05-12 : 03:13:13
Rather sacreligiously I used Excel to do this. Generated a random number and formatted it as a date, you can also set an upper and lower bound for the number


steve

-----------

Oh, so they have internet on computers now!
Go to Top of Page

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2006-05-12 : 11:31:48
Good idea Steve. Had I thought of it, I would have done the same thing. I've often spent time creating stored procedures and functions in SQL Server,rather than going for a quick solution using other software, just so that 'I can use them again one day'. Have I used most of them again?...No!
Thanks for your input.
cheers,
Sam
Go to Top of Page
   

- Advertisement -