Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generate Dates between date ranges

People also ask

How can I get a list of dates between two dates?

We can get the dates between two dates with single method call using the dedicated datesUntil method of a LocalDate class. The datesUntill returns the sequentially ordered Stream of dates starting from the date object whose method is called to the date given as method argument.

How do I generate days between two dates in SQL?

SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.

How do I create a date sequence in SQL?

To generate a date sequence, you can use the number sequence technique and the T-SQL function DATEADD(). Suppose you need to generate a sequence of dates from 1/1/2001 to 3/1/2001. First, you generate the sequence of numbers from 0 to 59.

How do I create a date between two dates in Excel?

To find the number of days between these two dates, you can enter “=B2-B1” (without the quotes into cell B3). Once you hit enter, Excel will automatically calculate the number of days between the two dates entered. Note that Excel recognizes leap years.


Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.


Try this if you are using SQL Server 2005 or newer:

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'09/01/2011')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '10/10/2011'
) SELECT
 [Date]
FROM
 Dates
 OPTION (MAXRECURSION 45)

A good example of cool stuff you can do with a CTE.


-- Declarations

DECLARE @dates TABLE(dt datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

-- Output

SELECT * FROM @dates

Here is a solution that does not require recursion, and at the same time, this table-valued function is re-usable in many queries without the need to repeat the declaration of boilerplate variables again. This is the only alternative, for those who don't want recursion.

Create this simple function:

CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
 @EndDate AS   DATE,
 @Interval AS  INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
    DECLARE @CUR_DATE DATE
    SET @CUR_DATE = @StartDate
    WHILE @CUR_DATE <= @EndDate BEGIN
        INSERT INTO @Dates VALUES(@CUR_DATE)
        SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
    END
    RETURN;
END;

And then select by:

select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)

I realize that this is an old thread, but I have to admit my dismay at the overabundance of recursive and looping solutions given here. I wonder just how many folks realize that recursion is nothing more than a very expensive loop? I understand the desire to create a Table-Valued Function, but I suggest that the following is far more efficient as it is set-based, without looping, recursion, or repeated single insert statements:

CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
    WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
        ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
        ,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    SELECT DATEADD(DAY, n-1, @StartDate)
    FROM cteTally
    WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO

Use MVJ's F_TABLE_DATE function, it is purely awesome:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Once you implement this just pass in start and end date and you can insert all dates between.