DevPinoy.org
A Filipino Developers Community
Sign in
|
Join
|
Help
Home
Blogs
Media
Forums
Groups
DevCast
clintz' blog
»
Creating Dummy Data
Attention:
Site upgrade failed last night. Will resume updates this weekend. Thanks!- Keith
Creating Dummy Data
clintz' blog
Home
Contact
Syndication
RSS for Posts
Atom
RSS for Comments
Email Notifications
Go
Recent Posts
Excel to SQL Server using TSQL
Tip for faster T-SQL coding
Creating Sudoku problems using TSQL
Creating Dummy Data
Tags
SQL Scripts
SQL Server
TSQL
View more
Archives
November 2006 (1)
September 2006 (2)
August 2006 (1)
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
Comments
trashVin
wrote
re: Creating Dummy Data
on 08-31-2006 12:30 AM
im just curious how your company reacted to the 14million waste....
Add a Comment
Name:
(required)
Website:
(optional)
Comments
(required)
Remember Me?
Enter the numbers above:
Copyright DevPinoy 2005-2008