title: CTE to get dates between two dates using SQL Server
body: <p>I want to fetch missing dates between two dates</p>
  
  <p>say <code>@maxDate = '2013-01-28'</code></p>
  
  <p>say <code>@curDate = GetDate()</code></p>
  
  <p>I have written CTE to do this. Here is my CTE:</p>
  
  <pre><code>create procedure EmpDate  
  as  begin  
  declare @curDate Date  
  set @curDate = GETDATE()  
  declare @maxDate Date  
  select @maxDate = MAX(EmpAttendance.Sdate)  
  from EmpAttendance
  
  ;with GetDates As  
  (  
  select 1 as counter, @maxDate as Date   
  UNION ALL  
  select counter + 1, DATEADD(day,counter,@maxDate)  
  from GetDates  
  where DATEADD(day, counter, @maxDate) &lt; @curDate  
  )  
  select Date from GetDates  
  end   
  go  
  </code></pre>
  
  <p>The result of this is   </p>
  
  <pre><code>Date  
  2013-01-28  
  2013-01-29  
  2013-01-30  
  </code></pre>
  
  <p>but I want </p>
  
  <pre><code>2013-01-29  
  2013-01-30   
  </code></pre>
  
  <p>Please help me out.</p>