Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Unanswered: Random Date Generation

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    how about
    Code:
    date(int(rand() * 3652059))

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    In the same fashion is there a way to generate random text only of n characters?

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    can you give an example, please ????
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    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

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •