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.