Share via

Dynamic Expression By Timezone Broke

B K 41 Reputation points
2022-03-25T12:30:36.257+00:00

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 Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,469 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,743 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee
    2022-03-28T16:13:55.333+00:00

    Hi @B K ,

    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.