New and Niche function in SQL Server 2022. Close cousin in DAX ( Power BI )

SQL Server 2022 introduced the new function GENERATE_SERIES. 

With this , we can generate series of values with the appropriate stop interval.

For this function , we need to be on SQL 2022. 

Here is the link to the documentation 

https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16

1> select @@version

2> go

-

Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)

        Aug 24 2023 02:40:55

        Copyright (C) 2022 Microsoft Corporation

        Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: ) (Hypervisor)

Here , in this example , we will use to generate dates between two dates. 

With the  following CTE , we are generating the list of dates between 10/14/2023 and 10/22/2023.

We are omitting the step size , as the default is 1. 

1> WITH

2>     v_param AS

3>     (   SELECT

4>             '2023-10-14'                                 AS StartDate ,

5>             '2023-10-22'                                 AS EndDate ,

6>             DATEDIFF( DAY , '2023-10-14' , '2023-10-22') AS Days

7>     )

8> SELECT

9>     DATEADD ( DAY , gs.value ,

10>     (   SELECT

11>             startdate

12>         FROM

13>             v_param ) ) AS Dates

14> FROM

15>     GENERATE_SERIES( 0 ,

16>     (   SELECT

17>             Days

18>         FROM

19>             v_param )) gs;

20>

21> go

Dates

-----

2023-10-14 00:00:00.000

2023-10-15 00:00:00.000

2023-10-16 00:00:00.000

2023-10-17 00:00:00.000

2023-10-18 00:00:00.000

2023-10-19 00:00:00.000

2023-10-20 00:00:00.000

2023-10-21 00:00:00.000

2023-10-22 00:00:00.000

Usually, I would like to wrap the functionality in a stored procedure ( in SQL Server / MariaDB ) and in a package ( in Oracle).

Here is the equivalent of the above CTE  in a stored procedure. 

1> CREATE PROCEDURE p_gen_dates

2>     @p_start_date DATE , @p_end_date DATE AS

3>     BEGIN

4>         SET NOCOUNT ON;

5> SELECT

6>     DATEADD ( DAY , gs.value , @p_start_date ) AS Dates

7> FROM

8>     GENERATE_SERIES( 0 , DATEDIFF( DAY , @p_start_date , @p_end_date) ) gs;

9> END

10> GO

1> EXEC  [dbo].[p_gen_dates]  '2023-10-14'  ,  '2023-10-16'

2> GO

Dates

-----

2023-10-14

2023-10-15

2023-10-16

This new function is niche and simple to use. 

Looks like , this function is borrowed from DAX.

In the Power BI Desktop , navigate to the "Table View" and click on the "New Table" .

That opens up a new window to create a DAX measure . Here we create a calculated table using "GENERATESERIES" function . In the example below , we created a table of 10 records , starting with 1 with a step size of 2 ( 1 is the default for step size) 

Comments Welcome!