0 out of 1 rated this helpful - Rate this topic

SWITCHOFFSET (Transact-SQL)

SQL Server 2008 R2

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 Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

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.

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

Reference

CAST and CONVERT (Transact-SQL)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD