I'll apologize in advance for the length. This probably should have been a normal post in the appropriate forum.
We have a web app with an Angular front end (as they said in 13th Warrior, I'd rather prefer a dragon), .NET Core 3.1 back end that calls multiple .NET 6 APIs. This is all built on the BFF design. For the uninitiated, this means the Angular component calls an Angular service that calls a .NET controller that calls a .NET service that calls the external API. The web app is hosted in Azure, location is set correctly. One or more of the APIs is hosted locally.
The app works fine in debug and when running in production for our time zone. The problem we are having is when a user from a different time zone (say Samoa) submits a transaction. The date showing in the database is one day off +- than it should. This is not a date that can be set as a default in the database. For this system it is important this date be correct for our time zone. I know the standard for storing dates is to use UTC and convert as needed, but I can't make that kind of change to the system. Way too many years of data and too many reports that depend on the date.
What I have tried:
None of us have really been down this road of dealing with different time zones. In the past all of our web apps have been hosted locally. We've been through a handful of iterations trying to solve this issue. I'm just about ready to pass all important dates as a formatted string and simply pass that to SQL Server and let it convert to a date.
Currently we are either converting all dates to a new date built from month, day, year, using a Kind of Local. Or using a custom method to determine if the passed date is UTC and converting to local time using DateTime.ToLocalTime() or it again builds a new DateTime from month, day, year, using Kind.Local. We also have tried converting the date value to our time zone. This can work depending on the start time zone. Sometimes it still fails.
Part of our process uses PayPal. We've been able to pass a valid date to PayPal in their specified format and they always display the correct date, but depending on the time zone the PayPal returned start date saved to the database can still be off +- one day.
The most frustrating part is getting this to work when debugging locally, but then seeing it fail once published to Azure.