Generate Dates Between a Date Range in SQL Server

Posted on by Ian

SQL Server 2022 introduced the GENERATE_SERIES() function, which enables us to create a series of values within a given range. Although this function is limited to just numeric values, we can still combine it with various other functions to create a series of date/time values.

Below are examples of how we can use the GENERATE_SERIES() function to help us get a list of all dates between two given date values.

Example

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

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

Result:

2036-12-27 00:00:00.000
2036-12-28 00:00:00.000
2036-12-29 00:00:00.000
2036-12-30 00:00:00.000
2036-12-31 00:00:00.000
2037-01-01 00:00:00.000
2037-01-02 00:00:00.000
2037-01-03 00:00:00.000

In this case, I used day as the first argument to DATEADD() in order to increment the day. The number of days to increment is provided by value, which is the value returned by the GENERATE_SERIES() function.

The third argument to GENERATE_SERIES() specifies the step amount – the amount that each value in the series is incremented from the previous value. I specified a step amount of 1, which resulted in the date values being incremented by 1 day.

Here it is again with value provided in a separate column:

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

So the first column shows us how many days each date is incremented by.

Changing the arguments provided to GENERATE_SERIES() will change the amount that our dates are incremented by.

Passing a string literal date to the DATEADD() function results in a datetime value being returned. If we have no need for the time portion, we can use CONVERT() or CAST() to convert the value to a date value.

Example:

SELECT
  value,
  CONVERT(date, DATEADD(day, value, '2036-12-27')) AS Result
FROM GENERATE_SERIES(0, 70, 10);

Result:

value       Result          
----------- ----------------
          0       2036-12-27
         10       2037-01-06
         20       2037-01-16
         30       2037-01-26
         40       2037-02-05
         50       2037-02-15
         60       2037-02-25
         70       2037-03-07

However, if the value isn’t a string literal date, then DATEADD() returns it using the same type that we passed to it (assuming it’s a valid type). Therefore, we wouldn’t need to convert it if it’s already of the correct type:

DECLARE @date date = '2036-12-27';
SELECT
  value,
  DATEADD(day, value, @date) AS Result
FROM GENERATE_SERIES(0, 70, 10);

Result:

value       Result          
----------- ----------------
          0       2036-12-27
         10       2037-01-06
         20       2037-01-16
         30       2037-01-26
         40       2037-02-05
         50       2037-02-15
         60       2037-02-25
         70       2037-03-07

Here, I passed a date value, and so a date value was returned by DATEADD().

Let’s increment the month instead:

DECLARE @date date = '2036-12-27';
SELECT
  value,
  DATEADD(month, value, @date) AS Result
FROM GENERATE_SERIES(0, 70, 10);

Result:

value       Result          
----------- ----------------
          0       2036-12-27
         10       2037-10-27
         20       2038-08-27
         30       2039-06-27
         40       2040-04-27
         50       2041-02-27
         60       2041-12-27
         70       2042-10-27

All we did was change day to month.

The same applies to any of the dateparts accepted by the DATEADD() function. For example, we could increment by year, quarter, week, hour, minute, etc.

Using the Integer Representation of the Dates

It’s also possible to pass the integer representation of date values to the GENERATE_SERIES() function.

Example:

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.

Using the Decimal Representation

We can also use the decimal/numeric value to capture any time portion:

SELECT 
  value,
  CONVERT(datetime, value) AS Result
FROM GENERATE_SERIES(
    CONVERT(decimal(8,3), CONVERT(datetime,'2036-12-27 01:35:25.000')),
    CONVERT(decimal(8,3), CONVERT(datetime,'2037-01-03 12:30:00.000'))
    );

Result:

value      Result                 
---------- -----------------------
 50034.066 2036-12-27 01:35:02.400
 50035.066 2036-12-28 01:35:02.400
 50036.066 2036-12-29 01:35:02.400
 50037.066 2036-12-30 01:35:02.400
 50038.066 2036-12-31 01:35:02.400
 50039.066 2037-01-01 01:35:02.400
 50040.066 2037-01-02 01:35:02.400
 50041.066 2037-01-03 01:35:02.400