question

BK-2724 avatar image
0 Votes"
BK-2724 asked ShaikMaheer-MSFT commented

Dynamic Expression By Timezone Broke

For over a year I've been running the following dynamic expression in order to get the most recent sunday in a specific format in eastern time. Then this week it broke:

@formatDateTime(convertTimeZone(adddays(utcnow(),sub(0,mod(dayOfWeek(utcnow()),7))),'UTC','Eastern Standard Time'), 'yyyy_MM_dd')

Here is the error message that I get:

The parameters and expression cannot be resolved for schema operations. Error Message: { "message": "ErrorCode=InvalidTemplate, ErrorMessage=In the function 'convertTimeZone', the value provided for the time zone id 'Eastern Standard Time' was not valid. " } Activity ID: bd429ac6-ac4b-4021-89d2-df05f1746f22

Is there a reason the time zone id for Eastern Time changed? And if so, any guidance on how to fix this would be greatly appreciated. Thanks in advance.

azure-data-factoryazure-synapse-analytics
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey,
Can you please try re executing again?

I tried executing the same logic and it got executed successfully:

186918-image.png


0 Votes 0 ·
image.png (10.2 KiB)
BK-2724 avatar image BK-2724 NandanHegde-7720 ·

thanks, are you using azure synapse or ADF? My use case is in synapse. maybe thats the issue

0 Votes 0 ·

Hi @BK-2724 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @BK-2724 ,

Thank you for posting query in Microsoft Q&A Platform.

I tried below expression in Synapse & ADF. Both the places it executed without any issues. Kindly check below screenshots.
@formatDateTime(convertTimeZone(adddays(utcnow(),sub(0,mod(dayOfWeek(utcnow()),7))),'UTC','Eastern Standard Time'), 'yyyy_MM_dd')

Synapse:
187535-image.png

ADF:
187632-image.png

Could you please try to re-run and see if that helps? If not, please share your implementation details step by step. So that I can try same and see. Thank you.

Hope this helps. Please let us know if any further queries.


Please consider hitting Accept Answer button. Accepted answers help community as well.


image.png (185.8 KiB)
image.png (188.4 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I was able to replicate what you put, so thanks for that. And my pipelines do work. But I want to flag that it does not work if you try to do it within Integration data187994-synapse-error.pngsets


0 Votes 0 ·
synapse-error.png (12.5 KiB)

Hi @ BK-2724 - Glad to know it worked for you. Please consider hitting Accept Answer button. Accepted answers help community as well.

Between, you mentioned that "it does not work if you try to do it within Integration data". Could you please elaborate more on this what you mean by that? I can try my side same and share updates accordingly. Thank you.

0 Votes 0 ·
BK-2724 avatar image BK-2724 ShaikMaheer-MSFT ·

when you try to run the expression in an integration dataset in synapse it doesn't work. when you run it in a pipeline it does. when i hit preview data there in an error

0 Votes 0 ·
Show more comments