Show all days between two dates

As I understood in your question the date 30/07/2015 would not enter, and the result would be:

01/07/2015
02/07/2015
03/07/2015
04/07/2015
05/07/2015

Follow the code that takes the date and the interval between them.

declare @startDate date;
declare @endDate date;

select @startDate = '20150701';
select @endDate = '20150705';

with dateRange as
(
  select dt = @startDate
  where @startDate < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) <= @endDate
)
select convert(varchar(MAX),dt,103)
from dateRange

A common practice in Data Warehouses is to create a dimension (usually a table) with all possible dates.

You can create a table with all the dates in your database and retrieve the result by a simple query :

SELECT date FROM allDates WHERE date BETWWEN 20150401 AND 20150501;

The advantage of using this type of table is in the performance gain if you need to make large joins with this date range.

You can create this table using a script and if you wish you can insert more columns according to your need.

A sample script follows:

BEGIN TRY
    DROP TABLE [dbo].[DimDate]
END TRY

BEGIN CATCH
    /*No Action*/
END CATCH

/**********************************************************************************/

CREATE TABLE    [dbo].[DimDate]
    (   [DateKey] INT primary key, 
        [Date] DATETIME,
        [FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
        [FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
        [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
        [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
        [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday 
        [DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
        [DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
        [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
        [DayOfWeekInYear] VARCHAR(2),
        [DayOfQuarter] VARCHAR(3),
        [DayOfYear] VARCHAR(3),
        [WeekOfMonth] VARCHAR(1),-- Week Number of Month 
        [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
        [WeekOfYear] VARCHAR(2),--Week Number of the Year
        [Month] VARCHAR(2), --Number of the Month 1 to 12
        [MonthName] VARCHAR(9),--January, February etc
        [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
        [Quarter] CHAR(1),
        [QuarterName] VARCHAR(9),--First,Second..
        [Year] CHAR(4),-- Year value of Date stored in Row
        [YearName] CHAR(7), --CY 2012,CY 2013
        [MonthYear] CHAR(10), --Jan-2013,Feb-2013
        [MMYYYY] CHAR(6),
        [FirstDayOfMonth] DATE,
        [LastDayOfMonth] DATE,
        [FirstDayOfQuarter] DATE,
        [LastDayOfQuarter] DATE,
        [FirstDayOfYear] DATE,
        [LastDayOfYear] DATE,
        [IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
        [IsWeekday] BIT,-- 0=Week End ,1=Week Day
        [HolidayUSA] VARCHAR(50),--Name of Holiday in US
        [IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
        [HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
    )
GO

/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date 

DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2018' --End Value of Date Range

--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
    @DayOfWeekInMonth INT,
    @DayOfWeekInYear INT,
    @DayOfQuarter INT,
    @WeekOfMonth INT,
    @CurrentYear INT,
    @CurrentMonth INT,
    @CurrentQuarter INT

/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)

INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)

--Extract and assign various parts of Values from Current Date to Variable

DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)

/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above

WHILE @CurrentDate < @EndDate
BEGIN

/*Begin day of week logic*/

         /*Check for Change in Month of the Current date if Month changed then 
          Change variable value*/
    IF @CurrentMonth != DATEPART(MM, @CurrentDate) 
    BEGIN
        UPDATE @DayOfWeek
        SET MonthCount = 0
        SET @CurrentMonth = DATEPART(MM, @CurrentDate)
    END

        /* Check for Change in Quarter of the Current date if Quarter changed then change 
         Variable value*/

    IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
    BEGIN
        UPDATE @DayOfWeek
        SET QuarterCount = 0
        SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
    END

        /* Check for Change in Year of the Current date if Year changed then change 
         Variable value*/


    IF @CurrentYear != DATEPART(YY, @CurrentDate)
    BEGIN
        UPDATE @DayOfWeek
        SET YearCount = 0
        SET @CurrentYear = DATEPART(YY, @CurrentDate)
    END

        -- Set values in table data type created above from variables 

    UPDATE @DayOfWeek
    SET 
        MonthCount = MonthCount + 1,
        QuarterCount = QuarterCount + 1,
        YearCount = YearCount + 1
    WHERE DOW = DATEPART(DW, @CurrentDate)

    SELECT
        @DayOfWeekInMonth = MonthCount,
        @DayOfQuarter = QuarterCount,
        @DayOfWeekInYear = YearCount
    FROM @DayOfWeek
    WHERE DOW = DATEPART(DW, @CurrentDate)

/*End day of week logic*/


/* Populate Your Dimension Table with values*/

    INSERT INTO [dbo].[DimDate]
    SELECT

        CONVERT (char(8),@CurrentDate,112) as DateKey,
        @CurrentDate AS Date,
        CONVERT (char(10),@CurrentDate,103) as FullDateUK,
        CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
        DATEPART(DD, @CurrentDate) AS DayOfMonth,
        --Apply Suffix values like 1st, 2nd 3rd etc..
        CASE 
            WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) _
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 _
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 _
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 _
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
            ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th' 
            END AS DaySuffix,

        DATENAME(DW, @CurrentDate) AS DayName,
        DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,

        -- check for day of week as Per US and change it as per UK format 
        CASE DATEPART(DW, @CurrentDate)
            WHEN 1 THEN 7
            WHEN 2 THEN 1
            WHEN 3 THEN 2
            WHEN 4 THEN 3
            WHEN 5 THEN 4
            WHEN 6 THEN 5
            WHEN 7 THEN 6
            END 
            AS DayOfWeekUK,

        @DayOfWeekInMonth AS DayOfWeekInMonth,
        @DayOfWeekInYear AS DayOfWeekInYear,
        @DayOfQuarter AS DayOfQuarter,
        DATEPART(DY, @CurrentDate) AS DayOfYear,
        DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
        DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, _
        DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
        (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), _
        @CurrentDate) / 7) + 1 AS WeekOfQuarter,
        DATEPART(WW, @CurrentDate) AS WeekOfYear,
        DATEPART(MM, @CurrentDate) AS Month,
        DATENAME(MM, @CurrentDate) AS MonthName,
        CASE
            WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
            WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
            WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
            END AS MonthOfQuarter,
        DATEPART(QQ, @CurrentDate) AS Quarter,
        CASE DATEPART(QQ, @CurrentDate)
            WHEN 1 THEN 'First'
            WHEN 2 THEN 'Second'
            WHEN 3 THEN 'Third'
            WHEN 4 THEN 'Fourth'
            END AS QuarterName,
        DATEPART(YEAR, @CurrentDate) AS Year,
        'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
        LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, _
        DATEPART(YY, @CurrentDate)) AS MonthYear,
        RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + _
        CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
        CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
        @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
        CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
        (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, _
        @CurrentDate)))) AS LastDayOfMonth,
        DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
        DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
        CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, _
        @CurrentDate))) AS FirstDayOfYear,
        CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, _
        @CurrentDate))) AS LastDayOfYear,
        NULL AS IsHolidayUSA,
        CASE DATEPART(DW, @CurrentDate)
            WHEN 1 THEN 0
            WHEN 2 THEN 1
            WHEN 3 THEN 1
            WHEN 4 THEN 1
            WHEN 5 THEN 1
            WHEN 6 THEN 1
            WHEN 7 THEN 0
            END AS IsWeekday,
        NULL AS HolidayUSA, Null, Null

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END

/*******************************************************************************************/


/* Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday ****/

-- Good Friday  April 18 
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Good Friday'
    WHERE [Month] = 4 AND [DayOfMonth]  = 18

-- Easter Monday  April 21 
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Easter Monday'
    WHERE [Month] = 4 AND [DayOfMonth]  = 21

-- Early May Bank Holiday   May 5 
   UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Early May Bank Holiday'
    WHERE [Month] = 5 AND [DayOfMonth]  = 5

-- Spring Bank Holiday  May 26 
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Spring Bank Holiday'
    WHERE [Month] = 5 AND [DayOfMonth]  = 26

-- Summer Bank Holiday  August 25 
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Summer Bank Holiday'
    WHERE [Month] = 8 AND [DayOfMonth]  = 25

-- Boxing Day  December 26      
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Boxing Day'
    WHERE [Month] = 12 AND [DayOfMonth]  = 26   

--CHRISTMAS
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Christmas Day'
    WHERE [Month] = 12 AND [DayOfMonth]  = 25

--New Years Day
    UPDATE [dbo].[DimDate]
        SET HolidayUK  = 'New Year''s Day'
    WHERE [Month] = 1 AND [DayOfMonth] = 1

--Update flag for UK Holidays 1= Holiday, 0=No Holiday

    UPDATE [dbo].[DimDate]
        SET IsHolidayUK  = CASE WHEN HolidayUK   IS NULL _
        THEN 0 WHEN HolidayUK   IS NOT NULL THEN 1 END



/*Update HOLIDAY Field of USA In dimension*/

    /*THANKSGIVING - Fourth THURSDAY in November*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Thanksgiving Day'
    WHERE
        [Month] = 11 
        AND [DayOfWeekUSA] = 'Thursday' 
        AND DayOfWeekInMonth = 4

    /*CHRISTMAS*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Christmas Day'

    WHERE [Month] = 12 AND [DayOfMonth]  = 25

    /*4th of July*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Independance Day'
    WHERE [Month] = 7 AND [DayOfMonth] = 4

    /*New Years Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'New Year''s Day'
    WHERE [Month] = 1 AND [DayOfMonth] = 1

    /*Memorial Day - Last Monday in May*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Memorial Day'
    FROM [dbo].[DimDate]
    WHERE DateKey IN 
        (
        SELECT
            MAX(DateKey)
        FROM [dbo].[DimDate]
        WHERE
            [MonthName] = 'May'
            AND [DayOfWeekUSA]  = 'Monday'
        GROUP BY
            [Year],
            [Month]
        )

    /*Labor Day - First Monday in September*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Labor Day'
    FROM [dbo].[DimDate]
    WHERE DateKey IN 
        (
        SELECT
            MIN(DateKey)
        FROM [dbo].[DimDate]
        WHERE
            [MonthName] = 'September'
            AND [DayOfWeekUSA] = 'Monday'
        GROUP BY
            [Year],
            [Month]
        )

    /*Valentine's Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Valentine''s Day'
    WHERE
        [Month] = 2 
        AND [DayOfMonth] = 14

    /*Saint Patrick's Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Saint Patrick''s Day'
    WHERE
        [Month] = 3
        AND [DayOfMonth] = 17

    /*Martin Luthor King Day - Third Monday in January starting in 1983*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Martin Luthor King Jr Day'
    WHERE
        [Month] = 1
        AND [DayOfWeekUSA]  = 'Monday'
        AND [Year] >= 1983
        AND DayOfWeekInMonth = 3

    /*President's Day - Third Monday in February*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'President''s Day'
    WHERE
        [Month] = 2
        AND [DayOfWeekUSA] = 'Monday'
        AND DayOfWeekInMonth = 3

    /*Mother's Day - Second Sunday of May*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Mother''s Day'
    WHERE
        [Month] = 5
        AND [DayOfWeekUSA] = 'Sunday'
        AND DayOfWeekInMonth = 2

    /*Father's Day - Third Sunday of June*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Father''s Day'
    WHERE
        [Month] = 6
        AND [DayOfWeekUSA] = 'Sunday'
        AND DayOfWeekInMonth = 3

    /*Halloween 10/31*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Halloween'
    WHERE
        [Month] = 10
        AND [DayOfMonth] = 31

    /*Election Day - The first Tuesday after the first Monday in November*/
    BEGIN
    DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), _
    DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

        INSERT INTO @Holidays(DateID, [Year],[Day])
        SELECT
            DateKey,
            [Year],
            [DayOfMonth] 
        FROM [dbo].[DimDate]
        WHERE
            [Month] = 11
            AND [DayOfWeekUSA] = 'Monday'
        ORDER BY
            YEAR,
            DayOfMonth 

        DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT

        SELECT
            @CURRENTYEAR = MIN([Year])
            , @STARTYEAR = MIN([Year])
            , @ENDYEAR = MAX([Year])
        FROM @Holidays

        WHILE @CURRENTYEAR <= @ENDYEAR
        BEGIN
            SELECT @CNTR = COUNT([Year])
            FROM @Holidays
            WHERE [Year] = @CURRENTYEAR

            SET @POS = 1

            WHILE @POS <= @CNTR
            BEGIN
                SELECT @MINDAY = MIN(DAY)
                FROM @Holidays
                WHERE
                    [Year] = @CURRENTYEAR
                    AND [Week] IS NULL

                UPDATE @Holidays
                    SET [Week] = @POS
                WHERE
                    [Year] = @CURRENTYEAR
                    AND [Day] = @MINDAY

                SELECT @POS = @POS + 1
            END

            SELECT @CURRENTYEAR = @CURRENTYEAR + 1
        END

        UPDATE [dbo].[DimDate]
            SET HolidayUSA  = 'Election Day'                
        FROM [dbo].[DimDate] DT
            JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
        WHERE
            [Week] = 1
    END
    --set flag for USA holidays in Dimension
    UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA  IS NULL THEN 0 WHEN HolidayUSA  IS NOT NULL THEN 1 END
/*****************************************************************************************/

Font

The following code prints all the dates in a range.

DECLARE @Data1 DATE, @Data2 DATE
SET @Data1 = '20150701'
SET @Data2 = '20150705'

SELECT CONVERT(varchar, DATEADD(DAY, number + 1, @Data1), 103) [Data]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number + 1, @Data1) < @Data2

I made a Fiddle .

Another tip:

/* SQLQUERY - MONTA LISTA COM DATA INICIAL ATÉ HOJE */
DECLARE  @TMP TABLE 
    (
    DATA DATETIME NULL
    )

DECLARE @DATAINI    DATETIME

SELECT  @DATAINI    = '2017.01.01 00:00:00.000'

INSERT INTO @TMP
SELECT @DATAINI

WHILE (SELECT MAX(DATA) FROM @TMP) <= GETDATE()
BEGIN
    INSERT INTO @TMP
    SELECT DATEADD(DD,1,MAX(DATA))
      FROM @TMP
END

SELECT * FROM @TMP 

DECLARE @dataInicio DATE 
DECLARE @dataFim DATE 

BEGIN 
    CREATE TABLE #calendario 
      ( 
         data      DATE, 
         yearmonth VARCHAR(9) 
      ) 

    SET @dataInicio = ( '1900-01-01' ) 
    SET @dataFim = ( Cast(Getdate() AS DATE) ) 

    WHILE @dataInicio <= @dataFim 
      BEGIN 
          INSERT INTO #calendario 
          VALUES      (@dataInicio, 
                       (SELECT Format(@dataInicio, 'MM-yyyy'))) 

          SET @dataInicio = Dateadd(day, 1, @dataInicio) 
      END 

    SELECT * 
    FROM   #calendario 
    ORDER  BY data DESC 
END