1

Does MS SQL Server have Series Generating Function(s) (aka generate_series) like Postgresql has. If not is there a basic way to implement the function? Timestamp version is preferred

I found this question which is an old one. Maybe there is a better solution in new versions.

3

A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT t.n
FROM t
ORDER BY T.n;

The query above will return an ordered list of values from 0 to 99.

For dates, you can implement it like this:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT DATEADD(DAY, t.n, '2019-01-01')
FROM t
ORDER BY T.n;

Which provides a list of dates starting at January 1st, 2019, and continuing for 100 days, ending on April 10th, 2019.

There are numerous other ways of generating a set like this, including generating a "numbers" table.

2

Not yet

But it is "under review" according to this post and this post on the Azure forums (although the Azure forums feedback isn't known for fast turnaround).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.