CODEWITHSUNDEEP
azureazure-data-factoryazure-synapseazure-synapse-pipeline
CuteeeeRabbit
CuteeeeRabbit

Reputation: 67

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

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.

Upvotes: 0

Views: 722

Answers (2)

Damien O - MSFT
Damien O - MSFT

Reputation: 66

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

Upvotes: 1

JayashankarGS
JayashankarGS

Reputation: 8140

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

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

Empty date array

enter image description here

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

enter image description here

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

enter image description here

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.

enter image description here

Output: enter image description here

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.

Upvotes: 0

Related Questions