generate random dates?

anushahanna
anushahanna used Ask the Experts™
on
how can you generate random dates in the past 100 years with tSQL?

for example, first row could bring back Mar 10 1987; second Apr 30 2004; third Dec 3 1957 etc

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
how about this?


SELECT dateadd(d,  30 * rand(), dateadd(m,  -1200 * rand()-1, getdate()))  as randdate

Author

Commented:
wow... looks perfect! can you please explain the logic so i can follow...
Commented:
this select returns a random date from the past 100 years(or 36500 days to be more accurate)

select dateadd(day,-cast(rand()*36500 as int),getdate())

it asks for a random number between 0 and 1 (rand()),
then multiplies it by 36500 (so you get a number between 0 and 36500),
then subtracts that number of days from the current date

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Most Valuable Expert 2011
Top Expert 2012

Commented:
working from the inside -out

getdate()
      - today's date

dateadd(m,  -1200 * rand()-1, getdate())
    -from today's date  go back in time a random number of months between 1 to 1200
      so this will produce a random month in the past century with today's date  (20th as of now)

dateadd(d,  30 * rand(), dateadd(m,  -1200 * rand()-1, getdate()))  
    - add a random 0-30 days to the random date generated before.
Most Valuable Expert 2011
Top Expert 2012

Commented:
hmmm, I got an overflow error trying to do large date adjustments,  so I switched to months + dates, I must have had a typo though.  

since it's an even century that does have leap years.   36525 might be a better factor to mulitply rand() by in order to cover the entire century

Author

Commented:
simply beautiful! both the logic was well thought out..

thanks experts - you stand out...

Author

Commented:
i am sorry i did not see the last reply, and i want to give points to 33951309 & 33951254, but seems like i missed it. i will ask mod to help out.. sorry ErezMor.

Author

Commented:
>>I got an overflow error trying to do large date adjustments
both the forumales you gave work for me every time- they will stand all the time, right?

Author

Commented:
>>36525

thanks
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, you should be able to use either one as long as you don't try to generate dates prior to 1753

Commented:
no worries, mate.
as long as you got it right, i'll be fine, thanks.

Author

Commented:
sdstuber,
why the year 1753?

Author

Commented:
ErezMor, you are very understanding - thanks; if it is easy to do, the mods will help with re-splitting.
Most Valuable Expert 2011
Top Expert 2012

Commented:
"why the year 1753?"

It's a cutoff year for adoption of the Gregorian calendar.
There were still some countries that didn't adopt it until after 1753, but "most" western countries had by then, in particular Great Britain (September of 1752).

Author

Commented:
Thanks alias99!

Thanks again to my experts...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial