0

I have started encountering this error today in an Azure SQL environment. There have been no changes to the Azure SQL environment from our end. No changes to schema objects, resources, etc.

I have two databases on the same azure sql server and this error occurs in any cross-database query, but does not happen within a single database.

For example, this query is successful:

select [LAST_MODIFIED], [LAST_MODIFIED] AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time'
from hbm_client --this is a local table in the database

And this query fails:

select [LAST_MODIFIED], [LAST_MODIFIED] AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time'
from hbm_client --local table
  inner join dbo.Account --table in other database, ignore the invalid join, it's not the cause

Any ideas on what could cause this change in behaviour given there were not database changes?

Antony
  • 3,781
  • 1
  • 25
  • 32

1 Answers1

1

I had the same problem. For some reason AT TIME ZONE works for a lot of my queries but failed for one of them. I haven't done a deep dive of the problem but I found a fix with TRY_CAST

select TRY_CAST([LAST_MODIFIED] AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time' AS DATETIME) as [LAST_MODIFIED]
from ...

You can swap out DATETIME for DATE or TIME if you prefer but if the value cannot be cast it will return null instead of an error

tgallei
  • 827
  • 3
  • 13
  • 22
Alastair
  • 26
  • 1
  • Thanks Alastair. This has helped me to work around the issue. I have a ticket open with MS for the root cause and will update here once/if known. – Antony Jun 08 '20 at 23:53
  • My team is noticing this too. We think it has to do with using the `DATETIMEOFFSET` in temp tables. Only our spocs with temp tables having rows with `DATETIMEOFFSET` columns inserted into them are erroring out. Still testing this out. – DankMetalSounds Jun 16 '20 at 15:02