Is the 'tzoffset' function no longer compatible within Synapse?

Anon101 51

Up until last night, executing the below script worked within our Synapse instance. However, since last night when I attempt execute the below script I receive the error message underneath.
Is the 'tzoffset' function no longer compatible within Synapse? No changes have been made to our Synapse instance in recent times. Thanks.

SELECT DATEPART(tzoffset, SYSDATETIMEOFFSET())

User's image

BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-17T20:16:33.9233333+00:00
Hello @Anon101,

Are you trying to run this on the dedicated SQL pool? or Serverless?

If you are tying to run it on the dedicated SQL pool, can you please let me know the version using

select @@Version
Anon101 51 Reputation points

2024-04-18T08:20:58.5+00:00
This is being executed on Serverless.
I've kept my explanation of the error short but the full story is..
My company has Dynamics as our transactional system. Eighteen months ago we migrated from Data Export Service (DES) to Synapse Serverless. I have created SQL Views within Synapse that our Power BI reporting extracts from.
When migrating to Synapse many of the functions used within the SQL Views built on Azure DES were not compatible with Synapse. I had to write workarounds for these. One of the functions that did work was the tzoffset used in the example below. This function had worked for eighteen months without an issue.

All of a sudden, as from the night of 16/04/2024, Synapse is now saying the 'tzoffset' function is no longer supported in Synapse.
I'm trying to understand why this is? I'm yet to establish if this is an issue with my company alone or if it is a Synapse issue world-wide.
Are you able to provide assistance on if the 'tzoffset' has been made incompatible since the 16/04/2024, or how to check why it's no longer supported on our Synapse Serverless?

To add, our version of Synapse is:
Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 11 2024 16:53:04 Copyright (C) 2022 Microsoft Corporation

Many thanks.
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-18T22:15:30.43+00:00

Hello Anon101,

Sorry, I am not able to reproduce the issue from my end(looks like this due to the version difference)

I created a new Serverless SQL DB but still the version pointed to 12.0.2000.8 Jan 19 2024

Can you please let me know your synapse region?

My version:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 19 2024 03:18:45 Copyright (C) 2022 Microsoft Corporation
Anon101 51 Reputation points

2024-04-25T15:21:53.21+00:00

I've been advised that Microsoft deployed a new version of code that caused the error. I've also been advised Microsoft have reverted the train and all should now work again - and it does.
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-25T15:40:57.7866667+00:00

Hello Anon101,

Thank you so much for the udpate as it helps the community look for answers to this issue.

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

1 answer

BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-25T15:41:30.93+00:00
Issue:

'tzoffset' function is no longer compatible within Synapse

error message:

Solution:

The issue occurred because Microsoft deployed a new version of code that caused the error. Microsoft has since reverted the changes, and the functionality is now working as expected.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment