Is it possible to create a date array variable dynamically in Azure Synapse Analytics?

Question

In Azure Synapse Analytics, I would like to use the pipeline function to set a variable of type array for the date from 12 months ago to current date. The date in the variable is the first day of each month. For example, a variable to be created this month might look like this

['2022-08-01','2022-09-01','2022-10-01','2022-11-01','2022-12-01','2023-01-01','2023-02-01','2023-03-01','2023-04-01','2023-05-01','2023-06-01','2023-07-01','2023-08-01']

And the variables to be created in September will be like this

['2022-09-01','2022-10-01','2022-11-01','2022-12-01','2023-01-01','2023-02-01','2023-03-01','2023-04-01','2023-05-01','2023-06-01','2023-07-01','2023-08-01','2023-09-01']

Any answer would be helped. Thank you.

Answer 1

You can use for each and append variable activity as below.

First, use Set variable to create empty array as below.

Empty date array

Next, create ForEach activity and add Append variable activity inside it.

Here, I given the items in range between 0 to 13 since you want dates for last 12 months. If you have this number in any variable or parameter add that in second argument of this range function.

Make sure to enable Sequential

Next, inside append variable add expression as below.

@addToTime(subtractFromTime(utcNow(),12,'Month','yyyy-MM-dd'),item(),'Month','yyyy-MM-dd')

Here, in the Name field choose the empty array variable you created initially. And in value field add above expression given.

Next, create new Set variable and create new array variable then give the value of the date array variable previously created.

Output:

Note: Give the date according to your requirement, but make sure not give 29,30 and 31 as these dates will not be in every month.

Answer 2

If you have a nearby SQL database of any kind, I would probably get it from there. Any good warehouse has a calendar table for just this type of thing. Here's a simple example using generate_series now availabe in SQL Server 2022 and Azure SQL DB:

DECLARE @startDate DATE = DATEFROMPARTS( YEAR(GETDATE()), MONTH(GETDATE()), 1 )

SELECT 
    DATEADD( Month, s.[value], DATEADD( Month, -13, @startDate ) ) yourDates
FROM generate_series( 1, 13 ) s
ORDER BY 1;

Get the data into the pipeline using a Lookup activity and Query mode.

I also got a method working with a For Each and Append variable activity, but it takes 16 seconds to run!? NB You can just set '01' in the format argument to get the first of the month:

Set variable startDate: @getPastTime(12, 'Month', 'yyyy-MM-01')
For Each Items: @range(0,13)
Append variable: @addToTime(variables('varStartDate'),item(), 'Month', 'yyyy-MM-01')

Append variable results