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
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!
