Point #1: Collations and time zones have nothing to do with each other. Collation affects only sort order and equality comparisons of strings, and has zero effect on dates. If you're going to give advice and quote documentation articles, you should know this already.
Point #2: The word developer documentation is an implicit "you". It's targetted at "the developer", who is not me. I'm not even a DBA! There is a whole team of DBAs that are not me ("you"). There are several dev teams for the suite applications hanging off this database, none of whom are me ("you").[1]
Did you notice how it straight up lies and says that GETDATE() and GETUTCDATE() return different values? Developers go to this page, see this, and assume "All is well", and then I get to spend 4 days of 12-hours of emergency scripting to fix this up.
Point #4: I love how I go on a rant about the mis-use of the word "you" in an Enterprise setting, and then your reply is "if you need to interpret date". Who is this mythical "you" person that is in charge of everything everywhere for all time in an 15K user enterprise!? I don't get to decide anything. I don't write the queries. Literally hundreds of people do, only half of which even work in this place! There are third-party report tools, integrations, import/export utilities, ETL, you name it. They all assume that local time is local time, not UTC.
Point #5: "Note the addition with the exception of Azure SQL Managed Instance: if you need server-level control over the time zone, you should use a Managed Instance." -- fantastic suggestion, why didn't I think of that? Oh I did, and discovered that Az SQL MI doesn't support zone-redundancy, which makes it a no-go for many Enterprise applications with strict uptime requirements. In this case it was absolutely rejected by the project steering committee (people not me).
Stop apologising for Azure's bad decisions.
AWS does this correctly. There is no excuse.
In 2022, if I want a zone-redundant PaaS offering for "Microsoft SQL", the only option is a non-Microsoft company: Amazon Web Services.
If you go with Azure, your data will be shredded the second three decades of muscle memory kicks in for some random developer OR you have to abandon your high availability requirements.
What kind of choice is that!?
Narrator: the type that makes customers stop recommending Azure and start recommending AWS.
[1] I went to the lengths of writing a Log Analytics alert to trigger on any use of the 'GETDATE()' function after having searched & replaced all uses of it with the workaround.[2] It gets triggered regularly. This is reality. Microsoft would like to simply pretend reality doesn't exist.
[2] The workaround on that page is wrong, it uses a fixed offset and is not daylight savings aware. I mean.. just.. oh my god how do you not see how bad this all is!?
The real workaround is something like this:
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime)
(I'm putting this here because random blogs are recommending using a scalar function, but that kills the performance in many common scenarios.)
I could picture you taking a deep breath before you wrote what came afterwards. I just wanna hug you and cry man! I've been through so much shit because of Azure's weird behaviour at work. Ruined weekends, Late night troubleshooting cos Azure is weirdly broken somewhere and this is not just isolated to one service. I'm talking about databases to app services to AKS to Azure Functions, Blob Triggers what the fuck not. I've had to do weird workarounds for issues which should not even be issues in the first place. Most of the production issues I've had with a fairly fairly complicated clusterfuck of a project was mostly Azure related issues. I don't even know what to think anymore!
> I went to the lengths of writing a Log Analytics alert to trigger
I've done this lol. Not for the date issue but something else.
As a reformed CTO of a rather large bank, your two posts above make me happy.
Not that you faced this. For that, just regretful empathy.
Rather, happy to recognize the “there is no excuse” attitude, the concrete and informed feedback, and the determination to keep digging into the problem till coming up with specific workarounds.
Neither the vendor nor your company understand what folks like this are worth.
Point #1: Collations and time zones have nothing to do with each other. Collation affects only sort order and equality comparisons of strings, and has zero effect on dates. If you're going to give advice and quote documentation articles, you should know this already.
Point #2: The word developer documentation is an implicit "you". It's targetted at "the developer", who is not me. I'm not even a DBA! There is a whole team of DBAs that are not me ("you"). There are several dev teams for the suite applications hanging off this database, none of whom are me ("you").[1]
Point #3: You linked to the "SQL Server" article, not the "Azure SQL" article. Okay, lets switch to the Azure SQL article: https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate...
Did you notice how it straight up lies and says that GETDATE() and GETUTCDATE() return different values? Developers go to this page, see this, and assume "All is well", and then I get to spend 4 days of 12-hours of emergency scripting to fix this up.
Point #4: I love how I go on a rant about the mis-use of the word "you" in an Enterprise setting, and then your reply is "if you need to interpret date". Who is this mythical "you" person that is in charge of everything everywhere for all time in an 15K user enterprise!? I don't get to decide anything. I don't write the queries. Literally hundreds of people do, only half of which even work in this place! There are third-party report tools, integrations, import/export utilities, ETL, you name it. They all assume that local time is local time, not UTC.
Point #5: "Note the addition with the exception of Azure SQL Managed Instance: if you need server-level control over the time zone, you should use a Managed Instance." -- fantastic suggestion, why didn't I think of that? Oh I did, and discovered that Az SQL MI doesn't support zone-redundancy, which makes it a no-go for many Enterprise applications with strict uptime requirements. In this case it was absolutely rejected by the project steering committee (people not me).
Stop apologising for Azure's bad decisions.
AWS does this correctly. There is no excuse.
In 2022, if I want a zone-redundant PaaS offering for "Microsoft SQL", the only option is a non-Microsoft company: Amazon Web Services.
If you go with Azure, your data will be shredded the second three decades of muscle memory kicks in for some random developer OR you have to abandon your high availability requirements.
What kind of choice is that!?
Narrator: the type that makes customers stop recommending Azure and start recommending AWS.
[1] I went to the lengths of writing a Log Analytics alert to trigger on any use of the 'GETDATE()' function after having searched & replaced all uses of it with the workaround.[2] It gets triggered regularly. This is reality. Microsoft would like to simply pretend reality doesn't exist.
[2] The workaround on that page is wrong, it uses a fixed offset and is not daylight savings aware. I mean.. just.. oh my god how do you not see how bad this all is!?
The real workaround is something like this:
(I'm putting this here because random blogs are recommending using a scalar function, but that kills the performance in many common scenarios.)