Question 223136 in Azure Question posted in
The official documentation to get you started can be found here.

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search