Generate random dates

Question

Generate random dates

vision59

SSCrazy

Points: 2601
More actions
May 12, 2008 at 9:00 pm

#71264

I have start date and end date fields in database where I want to scramble the dates. How to create random dates for start date and end date should be 1 day less than start date. For example start date be 10/2/1990 and end date will be 10/3/1990.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

James Horsley Hall of Fame Points: 3196 More actions · Answer 1

select cast(cast(RAND()*100000 as int) as datetime) will give you a random date between 1 Jan 1900 and 16 oct 2173 - you can see the range from

select cast(cast(0*100000 as int) as datetime)

and

select cast(cast(1*100000 as int) as datetime)

if that range is not OK you need to tweak the multiplier 100000 to some other value - you can determine the value with e.g.

Select cast(cast('1 Jan 2010' as datetime) as int)

obviously substitute the date you want as upper limit

that will give you a startdate - enddate is just dateadd(d,1,startdate)

James Horsley
Workflow Consulting Limited

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 2

Or you can use Dateadd() with other values (hour, minute, etc) and randomly add those to the beginning date.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Please don't cross-post. This has already been answered over here:

http://www.sqlservercentral.com/Forums/FindPost499535.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?