Show every day between two dates

Asked

Viewed 10,525 times

6

I need to display all dates that exist between two set dates.

I do not need the information of the difference between these dates, nor the data that are in the interval between them, but show the dates for each day of the interval.

Example:

Data de inicio: 01/07/2015
Data de fim: 05/07/2015
Resultado:
01/07/2015
02/07/2015
03/07/2015
04/07/2015
05/07/2015

5 answers

9


From what I understand 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

Follows code that picks up 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

3

A common practice in Data Warehouses is the creation of a dimension (usually a table) with all possible dates.

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

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 that range of dates.

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

Follows a script for example:

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
/*****************************************************************************************/

Source

  • 2

    I have seen such a solution in a project, and it was made for performance issues. It worked (and it works) very well.

2

Another suggestion:

/* 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 

2

The following code prints all 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.

  • I couldn’t see your fiddle, =/

  • @Erloncharles I fixed the link. See now.

  • It worked out, very good solution too, but wanted to understand a little more about it, the main doubt is what it would be type = 'P'?

  • 1

    @Erloncharles spt_values is one of those mysterious tables whose documentation is half Rcana. Basically, what it does in this code is to serve as an incrementer. type = P I am indicating that I want a sequence packed, that is, a numerical sequence starting at 0 and ending at 2047. Here’s an excellent explanation about her.

  • Very good, thank you

  • 1

    Excellent. I had no knowledge about the spt_values and I found very interesting the use.

Show 1 more comment

0

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 

Browser other questions tagged

You are not signed in. Login or sign up in order to post.