DEV Community

Slava Rozhnev
Slava Rozhnev

Posted on

Generate Date Series in popular Databases

In this post I want to answer to frequently asked question: How I can generate date series between to particular dates?

Generating a date series between two particular dates can be done using different methods depending on the relational database management system (RDBMS) you are using. I'll provide examples for a few popular RDBMS systems: MySQL, PostgreSQL, and Microsoft SQL Server.

Please note that the syntax might slightly differ based on the specific version of the RDBMS you're using, so you should consult the documentation for your specific version if you encounter any issues.


Legacy MySQL (5.7.*)
The old MySQL doesn't have built-in functions to generate a date series, so you might need to use a temporary table or a numbers table. Here's an example using a numbers table approach:

-- Insert numbers up to the desired range
INSERT INTO Numbers VALUES (0), (1), (2), ...;  

    DATE_ADD('start_date', INTERVAL n DAY) AS generated_date
FROM Numbers
    DATE_ADD('start_date', INTERVAL n DAY) <= 'end_date';

Enter fullscreen mode Exit fullscreen mode

Just replace 'start_date' and 'end_date' with your desired start and end dates and try it on

In Modern MySQL 8.0.*, you can use a Common Table Expression (CTE) to generate a date series between two particular dates. Here's how you can do it:

SET @start_date = '2022-01-01';
SET @end_date = '2022-01-31';

    SELECT @start_date AS generated_date
    SELECT DATE_ADD(generated_date, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE generated_date < @end_date
SELECT generated_date
FROM DateSeries;
Enter fullscreen mode Exit fullscreen mode


  1. The WITH RECURSIVE clause defines the CTE named DateSeries.
  2. In the initial SELECT statement within the CTE, we set the anchor value to the start date.
  3. In the recursive SELECT statement, we use the DATE_ADD function to increment the date by one day for each iteration.
  4. The WHERE clause in the recursive SELECT statement ensures that the recursion continues until the generated date is less than the end date.
  5. Finally, the outer SELECT statement selects all the generated dates from the CTE.

Remember that recursive queries can be resource-intensive, so use them cautiously and only when necessary. Try the query here


PostgreSQL has the generate_series function that makes this task easy:

SELECT generate_series('2022-01-01'::date, '2022-01-31'::date, '1 day') AS generated_date;
Enter fullscreen mode Exit fullscreen mode

Replace 'start_date' and 'end_date' with your desired start and end dates.

Microsoft SQL Server

SQL Server also has a similar approach using the sys.dates system table and the DATEADD function:

DECLARE @start_date DATE = '2022-01-01'
DECLARE @end_date DATE = '2022-01-31'

    (DATEDIFF(day, @start_date, @end_date) + 1)
    generated_date = DATEADD(day, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @start_date)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
Enter fullscreen mode Exit fullscreen mode

Since SQL Server 2022 where implemented GENERATE_SERIES function you can use it for generate dates series too in next way:

    DATEADD(day, value, '2022-01-01') AS Date
FROM GENERATE_SERIES(0, DATEDIFF(day, '2022-01-01', '2022-01-31'))
Enter fullscreen mode Exit fullscreen mode


SELECT DATE '2022-01-01' + LEVEL - 1 AS generate_series
FROM dual
CONNECT BY LEVEL <= DATE '2022-01-31' - DATE '2022-01-01' + 1
Enter fullscreen mode Exit fullscreen mode

Another cool method:

SELECT TRUNC (DATE '2023-01-01' + ROWNUM) dt
Enter fullscreen mode Exit fullscreen mode

If you know more methods to get date series in different RDBMS, please post in comments

Top comments (0)