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

• James Horsley

  Hall of Fame

  Points: 3196

  More actions

  May 15, 2008 at 8:54 am

  #815940

  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: 738140

  More actions

  May 15, 2008 at 9:20 am

  #815973

  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

  May 15, 2008 at 9:43 am

  #816005

  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?