Find all the dates in a date range ? TIP #100


It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = ‘2005-07-01’
DECLARE @EndDate   AS DATE = ‘2005-07-29’
DECLARE @tblDateRange AS TABLE (salesDate DATE)
SET @SeedDate = ‘2005-07-01’
WHILE @SeedDate <= @EndDate
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
SELECT * FROM @tblDateRange


Now second and interesting approach

DECLARE @StartDate AS DATE = ‘2005-07-01’
DECLARE @EndDate   AS DATE = ‘2005-07-29’
DECLARE @tblDateRange AS TABLE (salesDate DATE)

AS (
SELECT @StartDate AS SalesDate
SELECT DATEADD(d,1, SalesDate) As salesDate
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)

SELECT * FROM @tblDateRange


These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.



Enjoy !!!

3 Replies to “Find all the dates in a date range ? TIP #100”

  1. First, use of a recursive CTE is not "simple", it's more advanced than a simple "While" loop. The use of a recursive CTE is elegant and efficient, but it's definitely above the level of a simple "While" loop.

    Second, why not be consistent with your naming convention? "@tbl" is a table type. CTE is a table type. If you name one table "@tblDateRange", shouldn't you name the CTE table "cteDates"?

  2. You can also use a tally table to generate those dates, however the CTE is probably a better approach. See an example of a tally implementation below (in a real life scenario the tally table should be a real table, not just a temporary object):

    CREATE TABLE #tally (N INT)

    INSERT INTO #tally(N)
    SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) – 1 N
    FROM sys.columns c1
    CROSS JOIN sys.columns c2

    DECLARE @StartDate DATE, @EndDate DATE, @Days INT

    SET @StartDate = '2005-07-01'
    SET @EndDate = '2005-07-29'
    SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)

    SELECT DATEADD(DAY, N, @StartDate) Date
    FROM #tally
    WHERE N <= @Days

    DROP TABLE #tally

  3. Here is a solution with out a loop or CTE.

    DECLARE @StartDate AS DATE = '2005-07-01'
    DECLARE @EndDate AS DATE = '2005-07-29'
    DECLARE @tblDateRange AS TABLE (salesDate DATE)

    INSERT INTO @tblDateRange
    select top (DATEDIFF(day,@StartDate,@EndDate)+1) dateadd(day,Row_number() over(partition by @StartDate order by @StartDate)-1,@StartDate) Dates
    FROM sys.columns C1, sys.columns C2

    select *
    from @tblDateRange

Leave a Reply

Your email address will not be published. Required fields are marked *