SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating dynamic dates from stored parameters/criteria


Generating dynamic dates from stored parameters/criteria

Author
Message
D. Clark
D. Clark
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 173
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.
drew.king1
drew.king1
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 61
Can you provide a sample output of what you want this to look like?
drew.king1
drew.king1
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 61
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85533 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
D. Clark
D. Clark
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 173
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.
D. Clark
D. Clark
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 173
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search