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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.