DEV Community

Cover image for SQL-Quick tip #5 - Create a sequence of date and time
Allan Simonsen
Allan Simonsen

Posted on

19 2

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);


Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Speedy emails, satisfied customers

Postmark Image

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

Try REST API Generation for MS SQL Server.

DreamFactory generates live REST APIs from database schemas with standardized endpoints for tables, views, and procedures in OpenAPI format. We support on-prem deployment with firewall security and include RBAC for secure, granular security controls.

See more!

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay