Introduction to the GENERATE_SERIES() Function in SQL Server

In SQL Server, the GENERATE_SERIES() function is a relational operator that returns a series of values between a given start and stop point. These are returned in a single-column table.

Although the GENERATE_SERIES() function only works with numeric values, we can combine it with other functions to create a series of dates.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x) and requires the compatibility level to be at least 160.

Syntax

The syntax goes like this:

GENERATE_SERIES ( start , stop [ , step ] )

So it requires a start and stop argument, and the step argument is optional.

The start and stop argument can be a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric. Both arguments must be of the same type. For example, if the start argument is an int, then the stop argument must be int as well.

Example

Here’s a quick example:

SELECT * FROM GENERATE_SERIES( 1, 5 );

Result:

value      
-----------
          1
          2
          3
          4
          5

We can see that the series of numbers started at the first argument and ended at the second argument. The numbers incremented by 1, which is the default step amount (when the start point is lower than the end point).

If the start point is lower than the stop point, then the default step amount is -1.

Specify a Step

We can use a third argument to change the step amount:

SELECT * FROM GENERATE_SERIES( 1, 5, 2 );

Result:

value      
-----------
          1
          3
          5

Here, we specified a step of 2, which resulted in just three values being returned.

Create a Decrementing Series

We can use a negative step to create a series that decrements:

SELECT * FROM GENERATE_SERIES( 5, 1, -1 );

Result:

value      
-----------
          5
          4
          3
          2
          1

Note that this requires that the first argument is greater than the second. Otherwise nothing would be returned.

The inverse applies to incrementing series: the first argument must be lower than the second argument, otherwise nothing is returned.

Actually, the step argument is also optional when creating a decrementing series. If the start point is lower than the stop point, then the default step amount is -1.

SELECT * FROM GENERATE_SERIES( 5, 1 );

Result:

value      
-----------
          5
          4
          3
          2
          1

So we really only need to specify the step if we want it to decrement by a different amount to the default of -1.

Fractions

The GENERATE_SERIES() function accepts the numeric and decimal types, so we can use fractions:

SELECT * FROM GENERATE_SERIES( 1.5, 7.5, 1.5 );

Result:

value
-----
  1.5
  3.0
  4.5
  6.0
  7.5

Date & Time Values

As mentioned, we can use the GENERATE_SERIES() function in conjunction with other functions to create a series of date/time values.

Here’s an example of using the DATEADD() function to increment the date by the value returned by the GENERATE_SERIES() function:

SELECT 
  value,
  DATEADD(day, value, '2036-12-27') AS Result
FROM GENERATE_SERIES(0, 7, 1);

Result:

value       Result                 
----------- -----------------------
          0 2036-12-27 00:00:00.000
          1 2036-12-28 00:00:00.000
          2 2036-12-29 00:00:00.000
          3 2036-12-30 00:00:00.000
          4 2036-12-31 00:00:00.000
          5 2037-01-01 00:00:00.000
          6 2037-01-02 00:00:00.000
          7 2037-01-03 00:00:00.000

In this example, the value column contains the value returned by the GENERATE_SERIES() function, and the Result column contains the result of the DATEADD() function (after it incremented the date by the value amount). In this case, we used day as the first argument to DATEADD() in order to increment the day.

Here’s another example that returns the same result:

SELECT 
  value,
  CONVERT(datetime, value) AS Result
FROM GENERATE_SERIES(
    CONVERT(int, CONVERT(datetime,'2036-12-27 00:00:00.000')),
    CONVERT(int, CONVERT(datetime,'2037-01-03 00:00:00.000'))
    );

Result:

value       Result                 
----------- -----------------------
      50034 2036-12-27 00:00:00.000
      50035 2036-12-28 00:00:00.000
      50036 2036-12-29 00:00:00.000
      50037 2036-12-30 00:00:00.000
      50038 2036-12-31 00:00:00.000
      50039 2037-01-01 00:00:00.000
      50040 2037-01-02 00:00:00.000
      50041 2037-01-03 00:00:00.000

In this example, the value column contains an integer representation of the date. This is the number of days since 1900-01-01. So, when we add the number in the first row (50034) to 1900-01-01, we end up with 2036-12-27 00:00:00.000. And adding 50035 (in the second row) results in 2036-12-28 00:00:00.000, and so on.

Zero Step

Passing zero for the step (third argument) results in an error:

SELECT * FROM GENERATE_SERIES( 1, 5, 0 );

Result:

Msg 4199, Level 16, State 1, Server 0a1d19831a86, Line 1
Argument value 0 is invalid for argument 3 of generate_series function.

Incrementing by zero would result in no series being generated, and so SQL Server returns an error when we try to do that.

NULL Arguments

No rows are returned if any of the arguments are NULL:

SELECT * FROM GENERATE_SERIES( 1, NULL );

Result:

value      
-----------

(0 rows affected)