Скачать или смотреть How to Convert UTC Time to Multiple Time Zones in SQL Server

Learn how to convert UTC time to multiple time zones using SQL Server, including specific examples for Brisbane and Sydney.
---
This video is based on the question https://stackoverflow.com/q/65819725/ asked by the user 'BARG' ( https://stackoverflow.com/u/11315066/ ) and on the answer https://stackoverflow.com/a/65820623/ provided by the user 'Martin Cairney' ( https://stackoverflow.com/u/10481318/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Convert UTC Time to Multiple Time zones

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Convert UTC Time to Multiple Time Zones in SQL Server

Working with different time zones can be challenging, especially when you need to convert Universal Time Coordinated (UTC) to local times for various regions. In SQL Server, there are efficient ways to handle time zone conversions. In this guide, we will explore how to convert UTC time to local times in Brisbane and Sydney, and we'll also provide insights into how to adjust for daylight saving time (DST).

The Problem

Imagine you are managing a database that stores timestamps in UTC format. You need to display this data in local time for different regions in Australia, specifically Brisbane and Sydney. Brisbane is typically in the 'E. Australia Standard Time' time zone, while Sydney is one hour ahead of Brisbane during daylight saving time.

Given the challenge of converting UTC time to these specific local times, let’s see how to achieve this using SQL Server.

The SQL Solution

Step 1: Get the Current UTC Time

First, you can retrieve the current UTC time in SQL Server using the GETUTCDATE() function. This function returns the current date and time in UTC.

Step 2: Convert UTC Time to Brisbane Time

Once you have the UTC time, you can convert it to Brisbane time by using the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

In the query above:

GETUTCDATE() fetches the current UTC time.

The AT TIME ZONE clause converts UTC time to the 'E. Australia Standard Time' to get the time in Brisbane.

Step 3: Convert UTC Time to Sydney Time

To get the local time for Sydney, you can modify the same query to reflect Sydney's time zone. Sydney, which is typically ahead of Brisbane by one hour during daylight saving time, can be accessed using the same method as follows:

[[See Video to Reveal this Text or Code Snippet]]

Understanding Time Zones in SQL Server

All possible time zones recognized by SQL Server are defined in the sys.time_zone_info system table. You can query this table to find the exact names and details of available time zones. This is a powerful feature of SQL Server that allows you to dynamically adjust time zone conversions depending on your application needs.

Tip: Always verify if daylight saving is active for the specified time zone, as that may result in an additional hour to the standard time.

Conclusion

By following the SQL snippets and steps provided in this article, you can easily convert UTC time to local times for various regions in Australia, such as Brisbane and Sydney. Utilizing SQL Server’s AT TIME ZONE functionality streamlines the process and ensures accuracy, especially when dealing with daylight saving changes.

Feel free to experiment with other time zones as well by referencing the sys.time_zone_info table to meet your application's needs better. Happy querying!