In SQL Server, the AT TIME ZONE clause is used to convert datetime values between different time zones. This feature is particularly useful when you need to display datetime values based on different regional settings or ensure that your application handles datetime data accurately across different time zones.
Here's how you can use the AT TIME ZONE clause:
Syntax
<datetime_value> AT TIME ZONE '<time_zone>'
Steps to Convert Datetime Values
1. Define the Original Time Zone: Identify the timezone of the datetime value you are starting with.
2. Convert to Target Time Zone: Use the AT TIME ZONE clause to convert the datetime to the desired time zone.
Example
Assume you have a datetime value in UTC and you want to convert it to Eastern Standard Time (EST).
Step 1: Define the UTC datetime
DECLARE @UtcDateTime DATETIME = '2023-10-10 13:00:00'; -- UTC datetime
Step 2: Convert to Eastern Time
SELECT
@UtcDateTime AS OriginalUTCDateTime,
@UtcDateTime AT TIME ZONE 'UTC' AS DateTimeWithTimeZone,
(@UtcDateTime AT TIME ZONE 'UTC') AT TIME ZONE 'Eastern Standard Time' AS EasternTimeDateTime
Key Points
1. Time Zones Available: You need to use valid time zone names. You can find the list of valid time zone names by querying sys.time_zone_info:
SELECT * FROM sys.time_zone_info;
2. Handling Daylight Saving Time: SQL Server takes into account daylight saving changes automatically when you convert time zones.
3. Storing Dates with Time Zone: If you're working with datetimeoffset, you can store timezone-aware datetime values.
Full Example
Here’s a complete example demonstrating the conversion of a UTC datetime to Eastern Time:
DECLARE @UtcDateTime DATETIME = '2023-10-10 13:00:00'; -- Let's assume this is a UTC datetime
SELECT
@UtcDateTime AS OriginalUTCDateTime,
@UtcDateTime AT TIME ZONE 'UTC' AS UtcWithTimeZone,
(@UtcDateTime AT TIME ZONE 'UTC') AT TIME ZONE 'Eastern Standard Time' AS EasternTimeDateTime,
(@UtcDateTime AT TIME ZONE 'UTC') AT TIME ZONE 'Pacific Standard Time' AS PacificTimeDateTime;
Sample Output
Assuming @UtcDateTime is 2023-10-10 13:00:00 UTC:
- OriginalUTCDateTime: 2023-10-10 13:00:00
- UtcWithTimeZone: 2023-10-10 13:00:00 (UTC)
- EasternTimeDateTime: 2023-10-10 09:00:00 (In Eastern Daylight Time)
- PacificTimeDateTime: 2023-10-10 06:00:00 (In Pacific Daylight Time)
Using the AT TIME ZONE clause makes managing time zones straightforward in SQL Server, ensuring that your datetime values reflect the correct local time when displayed.