Generating Date Sequence With SQL Query

The task is to create SQL query that will produce a date sequence starting from @startDate till @endDate including, i.e. 2017-01-01, 2017-01-02, 2017-01-03 and so on.

There is a couple of obvious solutions.

1. DATEADD() with integer sequence where the integer sequence is an increment

Please take care how many records are in sys.all_objects, it won’t generate a long sequence. If you need more, use a self cross join as shown in Generating Integer Sequence With SQL Query.

2. Recursive query

The last option is very important because SQL Server limits the recursion level to 100 by default.

Leave a Reply

Your email address will not be published. Required fields are marked *