I dont know if this is what you have in mind but You could use dateadd function to do that.
Create table #tbDate (id int identity, dt datetime)
declare @effectiveDate datetime
set @effectivedate = '2007-12-15 00:00:00.000'
While (@effectivedate > '2007-12-01')
begin
Insert into #tbDate (dt)
values ( @effectivedate )
set @effectivedate = dateadd(d,-1,@effectivedate)
end
Select * from #tbDate
-Roy