Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating dynamic dates from stored parameters/criteria Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 4:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:11 AM
Points: 38, Visits: 163
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.
Post #1584076
Posted Friday, June 20, 2014 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 14, Visits: 56
Can you provide a sample output of what you want this to look like?
Post #1584309
Posted Friday, June 20, 2014 6:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 14, Visits: 56
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


Post #1584319
Posted Friday, June 20, 2014 9:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1584406
Posted Friday, June 20, 2014 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:11 AM
Points: 38, Visits: 163
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.
Post #1584415
Posted Friday, June 20, 2014 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:11 AM
Points: 38, Visits: 163
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.
http://www.sqlservercentral.com/articles/Test+Data/88964/


Thanks I'll take a look. It looks like some of it might be helpful.
Post #1584422
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse