Rand() function for integer


select Cast(rand() * 100 as int) + 1 as "1 to 100"

I don't understand why would I add 1 to Cast(rand() * 100 as int).
Can't 'Cast(rand() * 100 as int)' itself generate an integer between 1 and 100?
Who is Participating?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Nope.  Rand() generates a random number from 0 to 1 exclusive, meaning it's never 0 or 1, so multiplying by 100 will result in numbers between 0.00whatever and 99.99
Lokesh B RDeveloperCommented:

The output of the RAND function will always be a value between 0 and 1.
If you want to generate a random integer number, all you have to do is multiply it by the maximum value you want generated and then get rid of the decimal places.  One way of getting rid of the decimal places is by CAST it to INT.

Here's an example of generating a random number with a maximum value of 999,999:

SELECT CAST(RAND() * 100 AS INT) AS [RandomNumber]

SELECT CAST(RAND() * 1000 AS INT) AS [RandomNumber]

SELECT CAST(RAND() * 10000 AS INT) AS [RandomNumber]

See more information here
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For some more examples, I have an article on SQL Server:  T-SQL recipe to create a million sample people which uses RAN() quite a bit to generate numbers for phone, zip, SSN, house numbers, and random letters.
Scott PletcherSenior DBACommented:
I'm not sure adding 1 is technically a safe way to do that, since I think it might be possible that the * 100 value cast to an int would already round up to 100, and when you add 1 you could get 101.  I haven't tested that, but logically it seems possible to me, since CAST does indeed round the value.

I believe it would be safer to use:

CAST(CEILING(RAND() * 100) AS int)

The CAST is still necessary because the CEILING will return a float as it retains the original data type, but the CEILING will have done all the rounding, the cast just converts it to an int data type.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.