CTE to get dates between two dates using SQL Server

Question

I want to fetch missing dates between two dates

say @maxDate = '2013-01-28'

say @curDate = GetDate()

I have written CTE to do this. Here is my CTE:

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) < @curDate  
)  
select Date from GetDates  
end   
go  

The result of this is

Date  
2013-01-28  
2013-01-29  
2013-01-30  

but I want

2013-01-29  
2013-01-30   

Please help me out.

Answer 1

Change

select 1 as counter, @maxDate as Date

to

select 1 as counter, DATEADD(day,1,@maxDate) as Date

To make it simpler though change the CTE

;with GetDates As  
(  
select DATEADD(day,1,@maxDate) as TheDate
UNION ALL  
select DATEADD(day,1, TheDate) from GetDates  
where TheDate < @curDate  
)
... 

Answer 2

DECLARE @STARTDATE      DATETIME;
DECLARE @ENDDATE        DATETIME;
DECLARE @YEARS          INTEGER;

SET @YEARS = 10;
SET @STARTDATE = '20170101';

SELECT  @ENDDATE = DATEADD(DAY,-1,DATEADD(YEAR,@YEARS,@STARTDATE));
DECLARE @FirstDayOfWeek INTEGER;

SET @FirstDayOfWeek = 6;

;WITH CTE_DATES
AS
(
    SELECT @STARTDATE AS [DATE],
    1 AS [Level]
    UNION ALL
    SELECT 
        DATEADD(DAY,1, [DATE] )  , [Level] + 1  
    FROM CTE_DATES
    WHERE [DATE] < @ENDDATE
)
SELECT 
    [DATE],
    DATENAME(dw,[Date]) AS Daynamelong,
    LEFT(DATENAME(dw,[Date]),3) AS DaynameShort,
    DATEPART(dw,[Date]) AS NaturalDayNumber,
    CASE WHEN DATEPART(dw,[Date]) >= @FirstDayOfWeek THEN  (DATEPART(dw,[Date]) - (@FirstDayOfWeek)) +1
    ELSE 
        ((DATEPART(dw,[Date]) - (@FirstDayOfWeek)) +1) + 7
    END AS SpecialDayNumber,
    [Level]
FROM 
    CTE_DATES 
ORDER BY
    [DATE] ASC
OPTION (MAXRECURSION 5000);