SQL-Quick tip #5 - Create a sequence of date and time

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Create a sequence of date and time

When working with data that is somehow related to dates and times you may need to generate a sequence of date to group by or select from.
The trick here is to use a recursive CTE (Common Table Expression). It is a bit tricky to read but what the CTE does is to make a union with one additional datetime for each recursion.

The code below will create a sequence of datetimes with 10 minutes interval between to dates.

Like recursion in general this can take up a lot of resources and be slow so I would not recommend using it in production code with high number of execution. But for data analysis and drilling into your data this trick can be very useful.

 DECLARE @stepSizeInMinutes INT = 10; -- Change this line to change the time interval DECLARE @from DATETIME2 = '2017-01-01 00:00:00.00000', @to DATETIME2 = '2017-01-04 23:50:00.00000'; -- Create Recursive Discrete Table WITH Recursive_CTE AS ( SELECT @from AS TimestampUtc UNION ALL SELECT DATEADD(MINUTE, @stepSizeInMinutes, TimestampUtc) FROM Recursive_CTE WHERE TimestampUtc < @to ) SELECT * FROM Recursive_CTE ORDER BY TimestampUtc OPTION (MAXRECURSION 0);