T-SQL 101: 64 Changing the offset of a datetimeoffset value in SQL Server T-SQL using SWITCHOFFSET

The datetimeoffset data type was added in SQL Server 2012 and allowed us to not only store date and time values, but to also store a time zone offset (from -14 hours to +14 hours). When you’re using this data type though, you might need to change a value from one time zone offset to another.  That’s the purpose of the SWITCHOFFSET function.

Look at the following query:

SYSDATETIMEOFFSET is being used to return the current date, time, and time zone offset for the server, but we’re also asking for the equivalent with a +7 time zone offset. (That was the current time in Seattle when the query was run). You can see the result here:

It’s important to understand that SQL Server is not really time zone aware, but we do have some functions that can help you work with time zones. SQL Server 2016 they added a view (sys.time_zone_info) that allows you to look at the time zones known to Windows. And it also shows the current offset and if daylight savings time is currently being used.

Here’s an example of querying that view:

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-04-06