ADF - Pipeline Expression Builder - Error using convertTimeZone()

Question

ADF - Pipeline Expression Builder - Error using convertTimeZone()

Vivek Komarla Bhaskar 956

I get the error attached when using the convertTimeZone() function in the pipeline expression builder. Any help here, please?

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-09-19T05:27:18.857+00:00

Hi @Vivek Komarla Bhaskar ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

1 answer

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-09-19T05:27:18.857+00:00

Hi @Vivek Komarla Bhaskar ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 1

Subashri Vasudevan 11,226

Hi @Vivek Komarla Bhaskar

Thanks for using MS Q&A platform to post your question.

From the screenshot you have shared, it looks like you are trying to use convertTimeZone() in the Linked service Base URL field, which expects an URL like "https://www.example.com/" which is the REST endpoint.

If you can provide more details on what you are trying to achieve, we can help you. In the given Linked Service setting, we cannot convert time zone.

Please write back to us with more details.

Thanks.

Vivek Komarla Bhaskar 956 Reputation points

2022-09-08T08:23:04.713+00:00

I get the same error even with an URL starting with "https//". You can probably give it a try with any endpoint and add convertTimeZone to it, you should get the same error like I do
Subashri Vasudevan 11,226 Reputation points

2022-09-08T09:01:49.447+00:00

Why do you want to use the convert function in a place where url is expected ?
Vivek Komarla Bhaskar 956 Reputation points

2022-09-08T09:31:28.393+00:00

The endpoint URL fetches me data based on the StartDateTime and EndDateTime. Hence I'm trying to pass date time values to the URL.
Subashri Vasudevan 11,226 Reputation points

2022-09-08T10:19:31.843+00:00

We cannot edit the url field in linked service to pass parameters.

Request you to check this blog for detailed explanation to pass parameters to rest api url.

In your case, while you add a pipeline parameter, you can use the formatdatetime() function in the parameter value.

Kindly let us know how it goes.
Vivek Komarla Bhaskar 956 Reputation points

2022-09-08T10:38:04.94+00:00

Hi @Subashri Vasudevan ,

Not sure when you meant it doesn't work. I have been editing URL field to pass datetime all this while using formatdatetime which works but when I use convertTimeZone it doesn't work.
The reason for me to use ConvertTimeZone is to avoid manual intervention in changing UTC+1 / UTC+0 during day light savings in London.

NOTE: This is only in the case of Linked services expression builder.

See below -
This works,

This doesn't work,
Subashri Vasudevan 11,226 Reputation points

2022-09-08T11:02:42.64+00:00

Hi,

Sorry for that.

Your first screenshot was misleading.

You can choose do the timezone conversion in a set variable activity and use it in the url expression..
Subashri Vasudevan 11,226 Reputation points

2022-09-09T14:41:49.113+00:00

Hi @Vivek Komarla Bhaskar

Any further updates? Are you able to fix the issue?
Vivek Komarla Bhaskar 956 Reputation points

2022-09-09T15:10:55.78+00:00

Hi @Subashri Vasudevan , I would request you to share me with any relevant screenshots on the method you suggested. I don't understand how to use a set variable / pass this to URL.
Subashri Vasudevan 11,226 Reputation points

2022-09-09T16:47:53.693+00:00

Hi,

Check this thread, it has the detailed steps.

https://learn.microsoft.com/en-us/answers/questions/602943/adf-rest-api-pass-dinamically-startdateamptime-and.html

Hope it helps.
Vivek Komarla Bhaskar 956 Reputation points

2022-09-12T15:36:04.263+00:00

Hi @Subashri Vasudevan ,

Following exactly the same and when using the convertTimeZone I again get the same error -
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-09-14T09:05:02.377+00:00

Hi @Vivek Komarla Bhaskar ,

The function @convertTimeZone() does not work in data preview as stated in the error.
Kindly use @formatDateTime() instead @formatDateTime(addminutes(addhours(utcnow(),5),30),'yyyy-MM-dd')

For more details, kindly check this post.

Please do consider clicking Accept Answer as accepted answers help community as well. Thanks
Vivek Komarla Bhaskar 956 Reputation points

2022-09-23T09:00:49.347+00:00

Hi @AnnuKumari-MSFT ,

Above solution suggested is what I have been using but the problem with this is - I will have to manually update the function to remove anything added to add hours after the day light savings are over. I want to avoid this manual work each year, and hence would request to suggest a solution I can use in here so that date time is always as per the Europe/London timezone (Be it during daylight savings / normal)
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-09-23T09:30:27.51+00:00

Hi @Vivek Komarla Bhaskar ,
In that case, you can go ahead and use @convertTimeZone() function , it will work as per the expectation , the only thing is data preview is not supported . However, you can check data in the sink and validate.
Vivek Komarla Bhaskar 956 Reputation points

2022-09-23T09:36:50.027+00:00

Hi @AnnuKumari-MSFT ,

@convertTimeZone() doesn't work - please see the screenshot. The main reason to raise this question is for this - that using @convertTimeZone() throws me with error and hence what options are available to avoid manual work
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-09-23T09:49:53.637+00:00

Hi @Vivek Komarla Bhaskar ,
You cannot add functions in the Base URL of the linked service . If you try to add dynamic content in Base URL of the linked service, it won't allow you to add functions. However, you can use functions in the relative URL option of the dataset.

Check the below videos:

BaseURL:

Relative URL:
Vivek Komarla Bhaskar 956 Reputation points

2022-09-23T10:00:10.757+00:00

Hi @AnnuKumari-MSFT ,

I would suggest you to verify using @convertTimeZone() in the relative URL, I have tried all these options. The relative URL will ultimately pass the value to base URL and I get the same error. Instead having this back and forth, I really request you to give it a try from your side and let me know what approach is working.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-10-06T06:50:30.037+00:00

Hi @Vivek Komarla Bhaskar ,

Apologies for delay in response. Could you please check if the below thread helps: Format utcnow() in Azure Data Factory

---------------

If it heps, please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-10-07T06:46:30.22+00:00

Hi @Vivek Komarla Bhaskar ,
Did you get a chance to try the above suggested approach? Looking forward to your response. Thankyou

Share via

ADF - Pipeline Expression Builder - Error using convertTimeZone()

1 answer

Your answer