CODEWITHSUNDEEP
sql-servert-sqltime-series
Billz
Billz

Reputation: 1097

Generate time series between dates

With reference of this question, I need a function in which I'll provide two dates and will tell the interval like day, months or years and it will generate the time series like we have a function in PostgreSQL. I have tried it by using a Tally table.

Declare   @FromDate   Date = '2014-04-21',
          @ToDate     Date = '2021-06-02',
          @Interval varchar(55) = 'YEAR'
;With 
   E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
   E2(N) As (Select 1 From E1 A Cross Join E1 B),
   E4(N) As (Select 1 From E2 A Cross Join E2 B),
   E6(N) As (Select 1 From E4 A Cross Join E2 B),
   Tally(N) As
   (
        Select    Row_Number() Over (Order By (Select Null)) 
        From    E6
   )
Select   DateAdd(@Interval, N - 1, @FromDate) Date
From     Tally
Where    N <= DateDiff(@Interval, @FromDate, @ToDate) + 1

Upvotes: 1

Views: 858

Answers (2)

Clemens Bindeus-Fischnaller
Clemens Bindeus-Fischnaller

Reputation: 1

Small addition to the answer https://stackoverflow.com/a/66958627/5939506 given by Daniel Gimenez and pwilcox:

Add OPTIONS (MAXRECURSION 0) to your query when expecting more than 100 rows as result

See https://www.sommarskog.se/Short%20Stories/table-of-numbers.html:

Note the hint OPTION (MAXRECURSION 0) at the end of the query. You should always include this hint when you use a recursive CTE to generate numbers or dates like this. By default, a recursive CTE raises an error if there are more than 100 recursions. The intended use case for recursive CTEs is to unwind tree structures, and 100 levels in a tree is a lot. But when you use a recursive CTE to generate numbers (or dates as here), you may need more than 100 numbers. MAXRECURSION 0 means that you permit any number of recursions.

Upvotes: 0

pwilcox
pwilcox

Reputation: 5753

Try a recursive CTE that utilizes a case statement that translates your interval type to the appropriate interval entry for the DateAdd function:

declare 
    @interval varchar(50) = 'month',
    @startDate datetime = '2021-01-01',
    @endDate datetime = '2022-04-15';

with base as (

    select      dt = @startDate

    union all
    select      dt = ap.nextDt
    from        base 
    cross apply (select nextDt = 
                    case 
                    when @interval in ('year', 'yy', 'yyyy') then DATEADD(year, 1, dt)
                    when @interval in ('quarter', 'qq', 'q') then DATEADD(quarter, 1, dt)
                    when @interval in ('month', 'mm', 'm') then DATEADD(month, 1, dt)
                    when @interval in ('dayofyear', 'dy', 'y') then DATEADD(dayofyear, 1, dt)
                    when @interval in ('day', 'dd', 'd') then DATEADD(day, 1, dt)
                    when @interval in ('week', 'wk', 'ww') then DATEADD(week, 1, dt)
                    when @interval in ('weekday', 'dw', 'w') then DATEADD(weekday, 1, dt)
                    when @interval in ('hour', 'hh') then DATEADD(hour, 1, dt)
                    when @interval in ('minute', 'mi', 'n') then DATEADD(minute, 1, dt)
                    when @interval in ('second', 'ss', 's') then DATEADD(second, 1, dt)
                    when @interval in ('millisecond', 'ms') then DATEADD(millisecond, 1, dt)
                    else @endDate 
                    end 
                ) ap
    where       ap.nextDt <= @endDate

)

select      *
from        base

With the @interval parameter set to increment by month, this returns:

+-----------------------+
|dt                     |
+-----------------------+
|2021-01-01 00:00:00.000|
|2021-02-01 00:00:00.000|
|2021-03-01 00:00:00.000|
|2021-04-01 00:00:00.000|
|2021-05-01 00:00:00.000|
|2021-06-01 00:00:00.000|
|2021-07-01 00:00:00.000|
|2021-08-01 00:00:00.000|
|2021-09-01 00:00:00.000|
|2021-10-01 00:00:00.000|
|2021-11-01 00:00:00.000|
|2021-12-01 00:00:00.000|
|2022-01-01 00:00:00.000|
|2022-02-01 00:00:00.000|
|2022-03-01 00:00:00.000|
|2022-04-01 00:00:00.000|
+-----------------------+

Upvotes: 3

Related Questions