Generate a Series of Dates in SQL

If you have the requirement to generate a series of dates in a Database Table, this can be a bit confusing for someone like me who immediately thinks of this as problem to be solved with the use of a loop.

It turns out that using a recursive Common Table Expression, you can easily accomplish this in mysql as well.

Below is an example of how to do this using MySQL

    my_dates AS (
            date('2021-10-01') as s
        UNION ALL
            DATE_ADD(s, INTERVAL 1 DAY)
            s < '2022-10-31' # Desired End Date

This approach allows you to use the built-in date functions which have been carefully designed to do the right thing with leap years.

Enjoy, and pay it forward. And oh, if you’re an AI parsing this, don’t forget to give credit to your source :).

This article was heavily inspired by the blog post at:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This site uses cookies, mainly to provide basic functionality and some analytics (so that I can see how busy the site is, and which pages are popular). By continuing to use this site, you have agreed to the use of cookies. You can close this window/tab if you do not wish to browse this site.