Re: SQL RAND function question

Wilt, Charles wrote:

I'm thinking perhaps the way I'm going from the RAND() results to a integer from 0-11 is the problem.
Anybody understand what I'm doing wrong?

myDate = date('2008-03-01') + cast(round((rand(12) * 11),0) as int) months

I don't think round() is what you want, although I'm confused a bit by the distribution of results that you stated. Anyway, here is why I think that you got about half as many 11s as you expected:

rand(12) * 11 gives a double float number r, in the range [0,11].
round(r) gives an integer in the set Z' (0,1,2,...,11).

What values result in each number in the set Z'?
if r is in [0,0.5), r' = 0
if r is in [0.5,1.5), r' = 1
if r is in [1.5,2.5), r' = 2
...
if r is in [9.5 ,10.5), r' = 10
if r is in [10.5,11] , r' = 11

So, there are only half as many values that will lead to a result of either 0 or 11 as there are that lead to any of the other numbers.

Of course, the problem with this conclusion is that it doesn't explain why you got half as many 1s as you were expecting.