Random Date Generation

vivekjdesai · #1 (**permalink**) 09-01-04, 11:02

I need to extract some test data from my database into an excel sheet. Test data records should have certain columns from employee database table plus randomly-generated birth dates.

So a sample test data sheet would be have first_name, last_name, dob_yyyy, dob_mm, dob_dd columns.

Here I would like to fetch first_name and last_name from employees table but dob columns have to be generated randomly.

Can anyone give me hints on how to go about random date generation.

thanks,
Vivek

n_i · #2 (**permalink**) 09-01-04, 13:22

how about

Code:

date(int(rand() * 3652059))

sathyaram_s · #3 (**permalink**) 09-01-04, 18:47

If you want your employees to be born between, say 1955 and 1985, then

values(date('1955-01-01') + INT(RAND()*30) YEARS + INT(RAND()*11) MONTHS + INT(RAND()*27) DAYS )

HTH

Sathyaram

macjoubert · #4 (**permalink**) 09-02-04, 12:49

In the same fashion is there a way to generate random text only of n characters?

sathyaram_s · #5 (**permalink**) 09-02-04, 12:56

can you give an example, please ????

macjoubert · #6 (**permalink**) 09-02-04, 14:16

I mean is there some way you can generate random alphabets

like char (rand () ABC )) ; => is there something like this?

which should return the following values for 3(n) iterations.

abc,bca,cba,.... and so on. Or something similar

Damian Ibbotson · #7 (**permalink**) 09-03-04, 06:31

Quote:

Originally Posted by macjoubert

I mean is there some way you can generate random alphabets

There's always a way! Don't know if this is the easiest way but it's the best I can do for now...

Code:

CREATE FUNCTION SHUFFLE (P_START_STRING VARCHAR(2000))
RETURNS VARCHAR(2000)
LANGUAGE SQL
RETURN
WITH SHUFFLE (START_STRING, SHUFFLE_STRING, NEXT_CHAR_POS, LVL) AS
(
 VALUES (
           P_START_STRING
         , CAST('' AS VARCHAR(2000))
         , INT(RAND()*(LENGTH(P_START_STRING)))+1
         , LENGTH(P_START_STRING)
        )
 UNION ALL
 SELECT INSERT(START_STRING,NEXT_CHAR_POS,1,'')
 ,      SHUFFLE_STRING||SUBSTR(START_STRING,NEXT_CHAR_POS,1)
 ,      INT(RAND()*(LENGTH(RTRIM(START_STRING))-1))+1
 ,      LVL - 1
 FROM SHUFFLE
 WHERE LVL > 0
)
SELECT SHUFFLE_STRING FROM SHUFFLE WHERE LVL = 0
;

Internet Services

Web Development

Digital Marketing

Consumer Tech