Export (0) Print
Expand All

TODATETIMEOFFSET (Transact-SQL)

 

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 )

expression

Is an expression that resolves to a datetime2 value.

System_CAPS_noteNote

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.

System_CAPS_noteNote

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();
SELECT TODATETIMEOFFSET (@todaysDate, -120);
-- 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

ADD
Show:
© 2015 Microsoft