SWITCHOFFSET (Transact-SQL)
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).
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
SWITCHOFFSET ( DATETIMEOFFSET, time_zone )
Arguments
DATETIMEOFFSET
Is an expression that can be resolved to a datetimeoffset(n) value.time_zone
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.
Return Type
datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.
Remarks
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 because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. In addition, 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 for GETDATE() and will produce a more efficient query plan.
DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00');
SELECT * FROM t
WHERE c1 > @dt OPTION (RECOMPILE);
Examples
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
);
GO
INSERT INTO dbo.test
VALUES ('1998-09-20 7:45:50.71345 -5:00');
GO
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00')
FROM dbo.test;
GO
--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