Was this page helpful?
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 translated from a datetime2 expression.

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

TODATETIMEOFFSET ( expression , time_zone )


Is an expression that resolves to a datetime2 value.


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


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.


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

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

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

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

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

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft