todatetimeoffset-transact-sql.md

title	TODATETIMEOFFSET (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/13/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology	t-sql
ms.topic	language-reference
f1_keywords	TO_DATETIMEOFFSET_TSQL SWITCH_TZ_TSQL SWITCH_TZ TO_DATETIMEOFFSET
dev_langs	TSQL
helpviewer_keywords	date and time [SQL Server], TODATETIMEOFFSET TODATETIMEOFFSET function functions [SQL Server], time functions [SQL Server], date and time time [SQL Server], functions
ms.assetid	b5fafc08-efd4-4a3b-a0b3-068981a0a685
author	MashaMSFT
ms.author	mathoma
manager	craigg
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

TODATETIMEOFFSET (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

Returns a datetimeoffset value that is translated from a datetime2 expression.

Transact-SQL Syntax Conventions

Syntax

TODATETIMEOFFSET ( expression , time_zone )  

Arguments

expression
Is an expression that resolves to a datetime2 value.

Note

The expression cannot be of type text, ntext, or image because these types cannot be implicitly converted to varchar or nvarchar.

time_zone
Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example '+13:00'. The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time_zone.

Note

If expression is a character string, it must be in the format {+|-}TZH:THM.

Return Type

datetimeoffset. The fractional precision is the same as the datetime argument.

Examples

A. Changing the time zone offset of the current date and time

The following example changes the zone offset of the current date and time to time zone -07:00.

DECLARE @todaysDateTime datetime2;  
SET @todaysDateTime = GETDATE();  
SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00');  
-- RETURNS 2007-08-30 15:51:34.7030000 -07:00  

B. Changing the time zone offset in minutes

The following example changes the current time zone to -120 minutes.

DECLARE @todaysDate datetime2;  
SET @todaysDate = GETDATE();  
SELECT TODATETIMEOFFSET (@todaysDate, -120);  
-- RETURNS 2007-08-30 15:52:37.8770000 -02:00  

C. Adding a 13-hour time zone offset

The following example adds a 13-hour time zone offset to a date and time.

DECLARE @dateTime datetimeoffset(7)= '2007-08-28 18:00:30';  
SELECT TODATETIMEOFFSET (@dateTime, '+13:00');  
-- RETURNS 2007-08-28 18:00:30.0000000 +13:00  

See Also

CAST and CONVERT (Transact-SQL)
Date and Time Data Types and Functions (Transact-SQL)
AT TIME ZONE (Transact-SQL)