Ensuring a randomly generated number is always 8 digits

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  March 19, 2020 at 3:46 pm

  #3735626

  Hi there

   

  I have inherited a piece of code which generates an Activation Code based on a seeded value as follows

  select cast

  (round(rand(31303258 * rand(DATEPART(millisecond,GETDATE()))) * 100000000,0) as int)

  where 31303258  is the seed or another 8 digit number

   

  Now what I have noticed is that not all the time, a 8 digit number is generated

  For example

  9505439 -- length 7

   

  How can I ensure that an 8 digit number is always generated?

  I tried this

  select cast

  (round(rand(0+31303258 * rand(DATEPART(millisecond,GETDATE()))) * 100000000,0) as int),

  Len(cast(round(rand(0+31303258 * rand(DATEPART(millisecond,GETDATE()))) * 100000000,0) as int))

   

  But that gives a 7 digit number sometimes

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  March 19, 2020 at 5:07 pm

  #3735649

  Rewriting the requirement: Generate a random positive integer value between 10000000 and 99999999

  Solution: Create a random positive integer value between 0 and 89999999, and add 10000000 to it.

  /* generate random value between 10000000 and 99999999 */
  SELECT ((ABS(CHECKSUM(NEWID())) % 89999999)) + 10000000 AS rndval

   

  Eddie Wuerch
  MCM: SQL

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  March 19, 2020 at 5:21 pm

  #3735652

  Thanks Eddie that works for me!