Skip to content
Home » How to generate a date range in Azure Synapse without Spark notebooks

How to generate a date range in Azure Synapse without Spark notebooks

Every since I’ve started using Synapse for an assignment, I’ve preferred using Spark Notebooks to get anything done. However, they take time to spin up, something I wanted to mitigate by using Synapse-native components. In this article, we’ll generate a date range without Spark notebooks.

It’s unnecessarily complicated, but you can definitely create a data range using Synapse/ADF activities only. Let’s dive right in.

First, you should create pipeline parameters which mark the start and the end of the date range. Both should be string.

You should also set the pipeline variables. The date_range variable is an array will contain the constructed date range. The last_date_intermediate will constantly update with the next date in the until loop we’ll set later.

Next, create an until activity and provide it the following expression. This means it will keep looping until the last item of the array variable date_range is the same as the end parameter.

@equals(last(variables('date_range')),pipeline().parameters.end)

Within the until activity, we’ll create two sequential activities: a set variable activity and an append variable activity.

While the until loop runs, the first activity, the set variable activity, will set the last_date_intermediate variable to the next date between the start and end date specified in the pipeline parameters.

@if(
    equals(length(variables('date_range')),0),
    pipeline().parameters.start,
    substring(
        addDays(
            concat(last(variables('date_range')),'T01:00:00Z'),
            1
        ),
        0,
        10
    )
)

This is what it looks like in the GUI:

The append variable activity should be parametrized with the following dynamic content. It will read the newly set value from the last_date_intermediate variable, and append it to the date_range array variable.

@variables('last_date_intermediate')

This is what it looks like in the GUI:

If you though you could eliminate the first set variable activity by doing the calculation on the last date from the date_range array, inside the append variable activity, think again. You’ll run into this error:

The expression contains self referencing variable. A variable cannot reference itself in the expression.

Your until activity is is basically done now. If you’d like to loop over the constructed array, you should use a ForEach activity. Its items should refer to the date range array variables.

@variables('date_range')

In the GUI:

Finally, inside the ForEach activity, you can refer to the individual dates of the date range, simply by referring to the @item().

The final result should look something like this. I’ve used a subpipeline in the ForEach activity, but choose whatever floats your boat.

12 thoughts on “How to generate a date range in Azure Synapse without Spark notebooks”

  1. авито работа волжский свежие вакансии
    от прямых вакансии мфц в нижнем
    тагиле работа водителем боровлянах
    работа в салавате на авито уборщица мурман
    сифуд магазин в коле режим работы

  2. значение карт таро двойка мечей перевернутая
    любовь дева по гороскопу знаки
    зодиака сбербанк россии
    сонник если во сне видеть огонь значение карт таро на
    ближайшее будущее, значение карт
    таро на будущее

  3. погода жуалы бурное, погода бурное
    на неделю рейтинг беспроводных наушников, беспроводные наушники на уши атаң келеді артыңда, мұқағали мақатаев туған елім
    бағдарламалық қамтамасыздандырудың өмірлік циклі, бағдарламалық қамтамасыздандыру дегеніміз не

  4. проходной балл на юриста ент,
    проходной балл ент 2023 жас жубайлар
    алтынай жорабаева, жас жубайлар жас отау скачать пайда көру
    перевод, жан серігі перевод на русский ташкент – бухара скоростной поезд, ташкент – хива поезд

  5. Hmm it seems like your website ate my first comment (it was super
    long) so I guess I’ll just sum it up what I wrote and say,
    I’m thoroughly enjoying your blog. I too am an aspiring blog blogger but I’m still new to
    the whole thing. Do you have any suggestions for newbie blog writers?
    I’d certainly appreciate it.

  6. яндекс тариф вместе, яндекс такси заказать по телефону менің күн тәртібім сабақ жоспары балабақшада, менің күн тәртібім 3 сынып топ дополнительных заработков,
    как заработать деньги школьнику
    14 лет лагерь астана для подростков

  7. еріктік қылықтың басты көрсеткіші:,
    адамның еркі және өзін-өзі реттеу психологиясы
    4400 4301 какой банк, 4405 63 какой
    банк аузына құм құйылды сөйлем, ар
    сөзіне тұрақты сөз тіркесі әлеуметтік жүйе еңбегінің авторы, әлеуметтанудағы алғашқы теориялық бағыты

Leave a Reply

Your email address will not be published. Required fields are marked *