Create DYNAMIC LIST OF DATES for use with other queries

1

Please login or register to vote for this query.

(click on this box to dismiss)

Specify any two dates and then join to this your table in order to return records even for days where there is no data.

Stack Overflow

Q&A for professional and enthusiast programmers

--Q: https://dba.stackexchange.com/q/65458/

--You could explicity state the start/stop dates:
--  declare @startdate date = '20180401'
--  declare @enddate date = '20180701'

--or, as an example, I want "90 days up to the most recent data in the SEDE database":

declare @enddate date = ( select max(creationdate) from comments ) --  Find the most recent post date...
declare @startdate date = dateadd(d,-90+1, @enddate)               --  ...and begin 90 days before that

create table #datelist ( [myDate] datetime )

while (@startdate <= @enddate) begin
  insert into #datelist
  select top 1 @startdate as [myDate]
  set @startdate = dateadd(d, 1, @startdate)
end


-----------------------
select * from #datelist

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results