We want a table that is a list of dates. We really only need the dates between today and one year from today at most, not much more and not much less.
So first, we are going take the current date (@CurrentDate), calculate up to the date we want (one year from today plus one more day so that our table will start at 5/16/2018 and end at 5/16/2019, naming it @YearLaterDate). Because of leap years, the difference might be 367 instead of 366, so we are going to make a variable (@DaysDiff) to help us know that. Once we have that difference, we can loop through up to that number and then insert into our example table.
Declare @CurrentDate date Declare @YearLaterDate date Declare @DaysDiff int Declare @DayCounter int Create Table tblDateExample (dtDate date) Set @CurrentDate = getdate() Set @YearLaterDate = DateAdd(year,1,@CurrentDate) Set @YearLaterDate = DateAdd(day,1,@YearLaterDate) Set @DaysDiff = DateDiff(day,@CurrentDate,@YearLaterDate) Set @Daycounter = 0 while @DayCounter < @DaysDiff begin Insert into tblDateExample (dtDate) VALUES (DateAdd(day,@DayCounter,@CurrentDate)) Set @DayCounter = @DayCounter + 1 end Select * From tblDateExample Where 1 = 1
Hurray! We've made our table! But wait. Once tomorrow comes along, we will not need 5/16/2018 anymore and will want to add in 5/17/2018. Use whatever means you have for a scheduled task and run the following query once a day. We are going to use many similar variables because we have similar uses. We know we want the table for, from today, to a year from today plus one more day, so use those same ones again. Instead of the difference between those though, we are going to get the maximum date in our example table. Why? To handle for mishaps where we might need to add two days, or thirty days, instead of one. So, now we get the difference between our current maximum and our desired maximum, loop through that difference instead and add any days accordingly. We'll make a quick delete of anything before the day before the current date as well and anything after in case extra dates were added somehow.
Our maintaining query:
Declare @CurrentDate date Declare @YearLaterDate date Declare @DayCounter int Declare @CurrentMax date Declare @MaxDiff int Set @CurrentDate = getdate() Set @YearLaterDate = DateAdd(year,1,@CurrentDate) Set @YearLaterDate = DateAdd(day,1,@YearLaterDate) Set @DayCounter = 0 Set @CurrentMax = (select max(dtDate) from tblDateExample) Set @MaxDiff = DateDiff(day,@CurrentMax,@YearLaterDate) delete from tblDateExample where dtDate < @CurrentDate; delete from tblDateExample where dtDate > @YearLaterDate; while @DayCounter < @MaxDiff begin Update tblDateExample Set dtDate = dtDate Where dtDate = DateAdd(day,@DayCounter,@CurrentMax) if @@rowcount = 0 begin Insert into tblDateExample (dtDate) VALUES (DateAdd(day,@DayCounter,@CurrentMax)) end Set @DayCounter = @DayCounter + 1 end
Now we have a table that keeps the dates for roughly up to a year from the current date to a year from that.