Random Number with Criteria

[teblack]

I have the need to build a random number, and I have found code to do that via the faq page. But I need to add some limits to that returned random number. First the random number needs to be greater than 100, but less than 999. I can get the less than 999 but have been unable to figure out the must be greater than 100. Here is the sql statement that I'm using. Any suggestions would be greately appreciated.


Declare @max int
Set @max = 999
Select convert(int, rand()* 100000) % @max


Thanks again for any help -


TBlack -

 

[pwilson]

Declare @max int
Set @max = 900
Select convert(int, rand()* 100000) % @max + 100


6 % 5 = 1
6 % 7 = 2
6 % 11= 5
6 % 12 = 0

So setting the max to 999 gives you 100 - 998 not 999

 

[JamesLean]

An alternative method is:

DECLARE @min int,
	@max int

--set min/max values you want (inclusive)
SET @min = 100
SET @max = 999

SELECT CAST(RAND() * (@max - @min + 1) + @min AS int)

--James