Chad Callihan walks us through using AT TIME ZONE in SQL Server:
Dealing with time zones in general can be a headache. Thankfully, SQL Server 2016 included a new clause to make working with time zones more manageable. Let’s look at a couple examples:
I’m in Eastern Standard Time so we’ll start here. Keep in mind that when no offset information is included, SQL Server is going to assume that the date value is in the target time zone.
This works best when your dates are stored in UTC, but Chad does show how to convert between two other time zones. This does not perform all that well when you need to convert a lot of rows, but if you’re doing one or two conversions, it’ll do just fine.