June 19, 2014 at 4:12 pm
Hello all,
Not sure if this is the best forum for this but since it is in the realm of testing I thought I would try here first. I am in the process of creating a script to test out certain aspects of a stored procedure. The stored procedure relies on various tables to return a result set. This means that I need to populate these tables with data for testing. I've already ruled out using a copy of production data since it may not be reliable (as the stored procedure might not be too reliable in the first place...garbage in garbage out).
Instead, I am creating record templates (stored in their own tables) that the testing script calls to populate the application database with dummy records for testing. My biggest problem has been date creation. I can't hard code since a date that might make sense now won't make sense 2 or 3 years (maybe even months) from now, and I want to populate these "templates" once. That way testing is a matter of just executing the script and comparing the results to expected results.
The question I am getting stumped on is what is the best way to store the parameters to generate the dates. The worst case is that I can have fields to store the years, months, and days. It can also store whether or not the date should occur at the beginning, 15th, end of the month that the calculation landed on. It could also use the day for the current day but the 29th through the 31st might cause some issues obviously. However that means adding a large number of fields for each date field.
I am thinking I can get away with just month and a day indicator (e.g. first, tenth, fifteenth, twentieth, last, or current day). I just can't get the feeling that I am reinventing the wheel and that there is something out there that can do all of this. However, googling dynamic dates, dates criteria, and etc doesn't shed too much on this subject?
Anyway I would be interested in any suggestions/opinions.
June 20, 2014 at 5:54 am
Can you provide a sample output of what you want this to look like?
June 20, 2014 at 6:12 am
Something I notice a lot of people forget is to get the last day of the month you just need to find the first day of next month and subtract one.
DECLARE @IDate Date = GETDATE()
Select @IDate, DATEADD(D,-1,DATEADD(M, DATEDIFF(M, -1, @IDate), 0)) IDateMonthLastDay
June 20, 2014 at 9:17 am
D. Clark (6/19/2014)
Hello all,Not sure if this is the best forum for this but since it is in the realm of testing I thought I would try here first. I am in the process of creating a script to test out certain aspects of a stored procedure. The stored procedure relies on various tables to return a result set. This means that I need to populate these tables with data for testing. I've already ruled out using a copy of production data since it may not be reliable (as the stored procedure might not be too reliable in the first place...garbage in garbage out).
Instead, I am creating record templates (stored in their own tables) that the testing script calls to populate the application database with dummy records for testing. My biggest problem has been date creation. I can't hard code since a date that might make sense now won't make sense 2 or 3 years (maybe even months) from now, and I want to populate these "templates" once. That way testing is a matter of just executing the script and comparing the results to expected results.
The question I am getting stumped on is what is the best way to store the parameters to generate the dates. The worst case is that I can have fields to store the years, months, and days. It can also store whether or not the date should occur at the beginning, 15th, end of the month that the calculation landed on. It could also use the day for the current day but the 29th through the 31st might cause some issues obviously. However that means adding a large number of fields for each date field.
I am thinking I can get away with just month and a day indicator (e.g. first, tenth, fifteenth, twentieth, last, or current day). I just can't get the feeling that I am reinventing the wheel and that there is something out there that can do all of this. However, googling dynamic dates, dates criteria, and etc doesn't shed too much on this subject?
Anyway I would be interested in any suggestions/opinions.
I don't know exactly what you're going for but the following article may provide some food for thought in the area of generating test data, specifically where dates are concerned.
http://www.sqlservercentral.com/articles/Test+Data/88964/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2014 at 9:37 am
I'll try to illustrate what I am trying to do.
If I were to run this test today. I would would want it to pull records from my template records to populate the actual records in the application database. For date based fields such as effective_date it might generate a date of 2014-09-20 but next year I would want the same template to generate 2015-09-20. On the other hand I need the flexibility to say that the date must allows fall on a certain day. So for something like plan_start date I would want it to generate 2014-09-01 but in 3 months I would want it to generate 2014-12-01.
The issue isn't so much generating the dates, but storing a date generation variables in such a way that allows the greatest flexibility in date generation without needed a whole slew of fields to get the desired flexibility. One way to look at it would be to use a C struct that would store multiple attributes could define how a date should be generated, but I really don't want to go down the path of creating custom data types in SQL Server as I am not familiar with that route.
That's why I was thinking I could get away with 3 fields per date in the templates something like this
Create bogus_template (
...
plan_start_date datetime, --used to hardcode the date. If null then generate
plan_start_date_months integer, -- Used to specify how many months from getdate() the generated date should be.
plan_start_date_day varchar(10), --lookup to specify if the date should fall on a certain day of the month
....
)
The possible options for the look up would be FIRST, TENTH, FIFTEENTH, TWENTIETH, LAST, ON, BEFORE, AFTER. Where the last three would specify if the day should fall on the same day as getdate() (i.e. a simple dateadd with the number of months), 5 days before the getdate() day, or 5 days after it.
My original issue though is that I might be making up a crazy system that I'll look back at a year from now and smack myself in the head. The thing is that I can't think of a better way that is concise enough so that it does litter 5 or more fields per date and complicates things.
June 20, 2014 at 9:49 am
Jeff Moden (6/20/2014)
I don't know exactly what you're going for but the following article may provide some food for thought in the area of generating test data, specifically where dates are concerned.
Thanks I'll take a look. It looks like some of it might be helpful.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply