👀 Views: 71 💬 Answers: 1 📅 Created: 2025-07-31

sql-server datetime timezone sql

I'm facing unexpected results when converting `DATETIMEOFFSET` values to different time zones using the `AT TIME ZONE` feature in SQL Server 2019. I have a table that stores event timestamps in UTC format as `DATETIMEOFFSET` and I need to convert these timestamps to 'Pacific Standard Time'. However, the results seem to be off by several hours. Here’s a simplified version of my table and the query I'm using: ```sql CREATE TABLE Events ( EventID INT PRIMARY KEY, EventName NVARCHAR(100), EventTime DATETIMEOFFSET ); INSERT INTO Events (EventID, EventName, EventTime) VALUES (1, 'Event A', '2023-10-01 12:00:00 +00:00'), (2, 'Event B', '2023-10-01 14:00:00 +00:00'); SELECT EventID, EventName, EventTime AT TIME ZONE 'Pacific Standard Time' AS LocalTime FROM Events; ``` When I run this query, I expect `Event A` to convert to 'October 1, 2023, 5:00 AM PDT' but instead, I get 'October 1, 2023, 7:00 AM PDT'. I’ve double-checked the time zone settings on my SQL Server and they're set to the correct time zone. I’ve also confirmed that the `EventTime` values are correctly stored in UTC. To troubleshoot, I tried the following: 1. Verified the time zone offsets using `SELECT SYSDATETIMEOFFSET();` to ensure that the server is recognizing the correct offset. 2. Tried using the `SWITCHOFFSET` function separately to see if it produces consistent results: ```sql SELECT EventTime, SWITCHOFFSET(EventTime, '-07:00') AS AdjustedTime FROM Events; ``` 3. Checked if the SQL Server instance has the latest updates installed, but I’m still experiencing the issue. Is there a specific reason why the `AT TIME ZONE` function is yielding incorrect results, or am I missing something in my query? Any insights would be greatly appreciated! This is part of a larger application I'm building. Thanks in advance! Is this even possible?