Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All



Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions



Is an expression that can be resolved to a datetimeoffset(n) value.


Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

Use SWITCHOFFSET to select a datetimeoffset value into a time zone offset that is different from the time zone offset that was originally stored. SWITCHOFFSET does not update the stored time_zone value.

SWITCHOFFSET can be used to update a datetimeoffset column.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan. For more information about the RECOMPILE query hint, see Query Hints (Transact-SQL).

DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00'); 

The following example uses SWITCHOFFSET to display a different time zone offset than the value stored in the database.

CREATE TABLE dbo.test 
    ColDatetimeoffset datetimeoffset
INSERT INTO dbo.test 
VALUES ('1998-09-20 7:45:50.71345 -5:00');
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00') 
FROM dbo.test;
--Returns: 1998-09-20 04:45:50.7134500 -08:00
SELECT ColDatetimeoffset
FROM dbo.test;
--Returns: 1998-09-20 07:45:50.7134500 -05:00
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft