Creating Dummy Data

Testing is one of the "must" in software development process.
Once, our client lost almost 14 million pesos in a month because of using an application that was developed in a hurry and tested only for 2 days. I feel sorry for them because i was the one who developed that application. I warned them though, that they must have a test run for atleast a month or two.
Anyway, when testing some application, you will need a dummy data.The most common dummy data that were used are Integer/Float, String and Date.

To create a random integer or float, you can use the RAND() function (from BOL: RAND() function returns a random float value from 0 through 1), but sometimes I get a value more than 1.
So, if you want to generate a random integer which is less than n (n is any integer), all you have to do is multiply the result of RAND() function to n.
So when you run,
   select rand() * 100
the result is always < 100.
When you want to generate a random number between two numbers, you can can create a query something like this

   declare @min int, @max int
   select @min = 10, @max = 20

   select (((@max + 1) - @min) * RAND()) + @min

To create a random character you can use CHAR() function.
This query will generate a random 10-letter string (capital letters)

   declare @min int, @max int, @nRand int, @cRand varchar(10)
   select @min = 65, @max = 90 -- (97 - 122 --> small letters)

   declare @i int
   set @i = 0
   set @cRand = ''
   while @i < 10
       begin
           select @nRand = (((@max + 1) - @min) * RAND()) + @min
           select @cRand = @cRand + char(@nRand)
           set @i = @i + 1
       end

   select @cRand

At first, i find it hard on generating a random date. When you try

   select cast(0 as datetime) -- it will return 1900-01-01

If that's the case, then all you have to do to generate a random date is generate a random number.
In Sql server, i think the full range of dates it can handle is from 01/01/1900   to 12/31/9999.
The average days in a year is 365.2422.

   select cast(365.2422 as datetime) -- will return 1901-01-01
   select cast(365.2422 * 100 as datetime) -- will return 2000-01-01
   select cast(365.2422 * 100 + 13 as datetime) -- will return 2000-01-14

Anyway, if you want to generate a random date in between two dates, you can try this script:

   declare @nRand int
    declare @Jan float, @Feb float, @Mar float, @Apr float
       , @May float, @Jun float, @Jul float, @Aug float
       , @Sep float, @Oct float, @Nov float, @Dec float

   select @Jan = 31
   , @Feb = @Jan + 28.2422
   , @Mar = @Feb + 31
   , @Apr = @Mar + 30
   , @May = @Apr + 31
   , @Jun = @May + 30
   , @Jul = @Jun + 31
   , @Aug = @Jul + 31
   , @Sep = @Aug + 30
   , @Oct = @Sep + 31
   , @Nov = @Oct + 30
   , @Dec = @Nov + 31

   -- select @Jan jan, @Feb feb, @Mar mar, @Apr apr
   --    , @May may, @Jun jun, @Jul jul, @Aug aug
   --    , @Sep sep, @Oct oct, @Nov nov, @Dec [dec]

   --to generate a random date between Aug 2005 and Feb 2005:
   select @nRand = ((@Aug + 1) - @Feb) * RAND() + @Feb
   select convert(varchar, cast((365.2422 * 105) - 31 + @nRand as datetime), 101)

There's a lot of way to create a random data in sql server. But those are some of the basic ways i know.

Posted Aug 31 2006, 02:55 PM by clintz

Filed under: SQL Scripts, TSQL, SQL Server

trashVin wrote re: Creating Dummy Data

on 08-31-2006 12:30 AM

im just curious how your company reacted to the 14million waste....

Name: (required)

Website: (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Syndication

Recent Posts

Tags

Archives

Comments

Add a Comment