SWITCHOFFSET (Transact-SQL)SWITCHOFFSET (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

傳回從已儲存的時區位移變更為指定新時區位移的 dateoffset 值。Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函數的概觀,請參閱日期和時間資料類型與函數 (Transact-SQL)For an overview of all Transact-SQLTransact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )   

引數Arguments

DATETIMEOFFSETDATETIMEOFFSET
這是可解析成 datetimeoffset(n) 值的運算式。Is an expression that can be resolved to a datetimeoffset(n) value.

time_zonetime_zone
這是採用 [+|-]TZH:TZM 格式的字元字串或代表時區位移之帶正負號的整數 (秒鐘),而且假設是日光節約感知且經過調整。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,其毫秒精確度為 DATETIMEOFFSET 引數的毫秒精確度。datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

RemarksRemarks

請使用 SWITCHOFFSET 來選取 datetimeoffset 值,以便進入與原本儲存之時區位移不同的時區位移。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 不會更新已儲存的 time_zone 值。SWITCHOFFSET does not update the stored time_zone value.

SWITCHOFFSET 可用來更新 datetimeoffset 資料行。SWITCHOFFSET can be used to update a datetimeoffset column.

使用 SWITCHOFFSET 搭配函數 GETDATE() 會導致查詢執行速度變慢。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. 若要解決此問題,請使用 OPTION (RECOMPILE) 查詢提示,強制查詢最佳化工具在下次執行相同的查詢時,重新編譯查詢計劃。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. 如需 RECOMPILE 查詢提示的詳細資訊,請參閱查詢提示 (Transact-SQL)For more information about the RECOMPILE query hint, see Query Hints (Transact-SQL).

DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00');   
SELECT * FROM t    
WHERE c1 > @dt OPTION (RECOMPILE);  
  

範例Examples

下列範例會使用 SWITCHOFFSET 來顯示與資料庫中儲存的值不同的時區位移。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  

另請參閱See Also

CAST 和 CONVERT (Transact-SQL) CAST and CONVERT (Transact-SQL)
AT TIME ZONE (Transact-SQL)AT TIME ZONE (Transact-SQL)