Random Date Generator - SQL Server Forums

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]

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.

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

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

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!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources