sigkappu
asked on
Generate a 6 digit random number in SQL server
I am switching from Access to SQL server and having some problems with changeing some of the syntax and functions. I need to return a random 6 digit integer.
This is what the old sql statment was (Access):
select format(rnd*1000000,'000000
This is what I what I have come up with (SQL Server):
select right('000000' + rand()*1000000,6)
Unfortunately i still do not get the leading zeros when applicable and sometimes i get a number with a trailing decimal point.
The only acceptable format for the number is "123456" or "001234" etc. and it must be random.
Any help would be greatly appreciated.
This is what the old sql statment was (Access):
select format(rnd*1000000,'000000
This is what I what I have come up with (SQL Server):
select right('000000' + rand()*1000000,6)
Unfortunately i still do not get the leading zeros when applicable and sometimes i get a number with a trailing decimal point.
The only acceptable format for the number is "123456" or "001234" etc. and it must be random.
Any help would be greatly appreciated.
Jeff Certain
select right('000000' + CAST(rand()*1000000 AS VARCHAR(6)),6)
ASKER
Chaosian, I get the following error when there is less than 6 digits (ie when there should be a leading zero) using your suggestion.
Arithmetic overflow error for type varchar, value = 52029.653939.
Arithmetic overflow error for type varchar, value = 52029.653939.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks like it is working great. I am now getting the leading zero's. Thanks for the quick help.
No prob. Glad I could be of assistance...