RE: SQL RAND function question

[<lgoodbar@xxxxxxxxxxxxxx>]

This gives me a range of 0-11.

select
date('2008-03-01') + cast(rand()*11 as decimal(2,0)) months
from sysibm/sysdummy1

Date expression
   10/01/08    

Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Tuesday, February 12, 2008 12:25
To: Midrange Systems Technical Discussion
Subject: SQL RAND function question

All,

 

I'm attempting to use SQL's RAND() function to randomly assign a number
from 0-11.  Actually, the 0-11
is used as a number of months to add to 2008-03-01.

 

Looking at a count of the results however seems to indicate I'm doing
something wrong:

 

Date                       count(*)

2008-03-01              13,234

2008-04-01               6,536

2008-05-01              13,262

2008-06-01              13,153

2008-07-01              13,474

2008-08-01              13,448

2008-09-01              13,245

2008-10-01              13,329

2008-11-01              13,358

2008-12-01              13,307

2009-01-01              13,542

2009-02-01               6,636

 

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

 

Thanks!

 

Charles Wilt 

Software Engineer

CINTAS Corporation - IT 92B

513.701.1307

wiltc@xxxxxxxxxx <mailto:wiltc@xxxxxxxxxx>