Reputation: 46222

Generate Dates between date ranges

I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11

So in this case the table would start from 09/01/11 and store each day till it got to 10/10/11 I was wondering if there was a slick way of doing this in SQL Server - I am currently using SQL Server 2008. Thanks

Upvotes: 63

Answers (15)

Richard Griffiths

Reputation: 838

Using @Abe Miesler's answer, for other's convenience I built it into a TVF for SQL Server 2008 onwards. It may help others - I had to find a way to include the CTE inside the TVF!

    --Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
CREATE OR ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
 @EndDate AS   DATE,
 @interval AS  INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
     BEGIN
         WITH Dates
              AS (
              SELECT [Date] = CONVERT( DATETIME, @startDate)
              UNION ALL
              SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
              FROM Dates
              WHERE Date < @EndDate)
              INSERT INTO @Dates
                     SELECT [Date]
                     FROM Dates
                     OPTION(MAXRECURSION 900);
         RETURN;
     END;

Upvotes: 0

Marcos Hernan Costa Manso Croc

Reputation: 7

I use the ANSI standard SQL dialect on Databricks and the code below it`s a simpler way of creating month rows:

SELECT
  EXPLODE(
    SEQUENCE(
      CURRENT_DATE() + MAKE_INTERVAL(0, -11),
      CURRENT_DATE() + MAKE_INTERVAL(0, 1),
      INTERVAL 1 MONTH))

Upvotes: -1

nucc1

Reputation: 374

This is pretty easy to do if your database supports recursive common table expressions.

Here is an example on MySQL 8.

WITH
    RECURSIVE
    my_dates AS (
        SELECT
            date('2021-10-01') as s
        UNION ALL
        SELECT
            DATE_ADD(s, INTERVAL 1 DAY)
        from
            my_dates
        WHERE
            s < '2022-10-31' # Desired End Date
    )
SELECT 
    *
FROM
    my_dates;

You can use DATE_SUB() if you want the dates to count backwards, for example and adjust the where clause accordingly. You can also increment the dates by more than one day as desired by simply doing the date calculation with more than one day interval.

Reference: https://nucco.org/2023/02/generate-a-series-of-dates-in-sql.html

Upvotes: -2

Octávio Lage

Reputation: 37

Recursive query is a good alternative when we cannot create functions in the database.

MySQL 8+ & MariaDB 10.2.2+

  WITH RECURSIVE dates AS (
    SELECT '2022-01-01' AS _day -- Your start date
    UNION ALL
    SELECT DATE_ADD(_day, INTERVAL 1 DAY)
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

Postgres 11+

  WITH RECURSIVE dates AS (
    SELECT DATE('2022-01-01') AS _day -- Your start date
    UNION ALL
    SELECT DATE(_day + INTERVAL '1 day')
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

To join these dates in your SELECT statement, you can use a JOIN dates ON true to replicate your rows for each date in your date range.

  [WITH statement according to your database]
  SELECT col1, col2, _day
  FROM my_table
  JOIN dates ON true

Upvotes: -1

sll

Reputation: 62484

-- Declarations

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

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

-- Query:

WHILE(@dateFrom <= @dateTo)
BEGIN
   INSERT INTO @dates 
   SELECT @dateFrom

   SELECT @dateFrom = DATEADD(day, 1, @dateFrom)
END

-- Output

SELECT * FROM @dates

Upvotes: 19

Stuart Ainsworth

Reputation: 12940

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.

EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here's some code to create and populate a table.

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.

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

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

Performant, and no recursion.

Upvotes: 84

AliNajafZadeh

Reputation: 1328

Try Following CODE:

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

Examining the performance, I found that using the CTE method has a better performance that I have shown in the figure. For this purpose, I used two queries and displayed the performance using the SQL Server tool.

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

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

Upvotes: 1

DanielG

Reputation: 1675

This is an old thread, but in case it helps anyone, this is what I use in modern versions of SQL Server that support CTE's. This also gives you the Day of the Week and it can be tweaked to give other values you may need (i.e. Quarter, Month, etc.).

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '1/1/2020'
SET @EndDate = '12/31/2020'
DECLARE @DayTable Table(theDate date, theDayOfWeek nvarchar(50));
WITH DayTable AS (SELECT CAST(@StartDate AS DATETIME) theDate, DATENAME(dw, @StartDate) theDayOfWeek UNION ALL SELECT DATEADD(dd, 1, theDate), DATENAME(dw,DATEADD(dd, 1, theDate)) FROM DayTable s  WHERE DATEADD(dd, 1, theDate) <= CAST(@EndDate AS DATETIME)) 
INSERT INTO @DayTable(theDate, theDayOfWeek) SELECT theDate, theDayOfWeek FROM DayTable OPTION (MAXRECURSION 365); 
SELECT * FROM @DayTable

Upvotes: 1

SQL RV

Reputation: 97

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

Upvotes: 9

Mukehp

Reputation: 11

CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)

  Insert into #ProductSales
  Values
  (373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
  (375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')

  ;WITH Dates AS (
        SELECT ProjectiD
        ,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
         ,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
         FROM #ProductSales
        UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
         [Date] = DATEADD(MONTH, 1, [Date])
        FROM
         Dates
        WHERE
         Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
 CAST([Date] as Date) Date
FROM
 Dates
 OPTION (MAXRECURSION 45)

Upvotes: -1

Lars Blumberg

Reputation: 21361

If for some reason you can't declare variables, such as when using derived tables in Looker, you can go like this:

select
  dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
  select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
  nbr - 1 <= datediff(
    day,
    convert(date, '2017-01-01'),
    convert(date, '2018-12-31')
  )

By the way, this is how your date series view could look like in LookerML:

view: date_series {
  derived_table: {
    sql:
      select
        dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
      from (
        select row_number() over (order by c.object_id) as nbr from sys.columns c
      ) nbrs
      where
        nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
  }

  dimension: date {
    primary_key: yes
    type: date
    sql: ${TABLE}.d ;;
  }
}

Upvotes: 1

sken130

Reputation: 350

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)

Upvotes: 15

SanH

Reputation: 19

Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);

While @StartDate<=@EndDate
begin
    SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))

    IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)

    INSERT INTO @tempDateRange (DateFrom,DateThru)
    VALUES 
     (
        @StartDate,
        @EndDate
     )

    SET @StartDate=DATEADD(DAY,1,@EndDate)

    IF @EndDate< '2016-12-31'
     IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;

select * from @tempDateRange

+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru

Upvotes: -1

Abe Miessler

Reputation: 85036

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.

Upvotes: 58

JonH

Reputation: 33141

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.

Upvotes: 1

Generate Dates between date ranges

Answers (15)

MySQL 8+ & MariaDB 10.2.2+

Postgres 11+

Related Questions