CODEWITHSUNDEEP
sql-servertimezonessmstimezone-offsetdatetimeoffset
Vincent Sels
Vincent Sels

Reputation: 2889

In SSMS, display DateTimeOffset value in local timezone

Isn't there a way to configure Sql Server Management Studio to, in query results, display the value of a DateTimeOffset column in the client's local timezone, instead of UTC?

Example: I'm in timezone +02:00. A value is stored as 2016-07-27 22:00:00.0000000 +00:00. Currently, this value is displayed as such in query results. I want it to be displayed (formatted) as 2016-07-28 00:00:00.0000000 +02:00 when executing this query on SSMS on my machine.

Currently I'm manually using something like SWITCHOFFSET(CONVERT(datetimeoffset, <DateColumn>), DATENAME(TzOffset, SYSDATETIMEOFFSET())) which is obviously very cumbersome.

If I'm not mistaken, in Oracle the NLS parameters on session level could be used for this. Is there something similar in Sql Server?

Upvotes: 2

Views: 2379

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241808

Neither SQL Server nor SSMS have any ability to work with the concept of a "session time zone" (like Oracle, MySql, etc. do).

Also, it wouldn't make sense to automatically convert the offset anyway, as the offset is actually part of the stored datetimeoffset value.

If you're using SQL Server 2016, or Azure SQL Database, you can use the new AT TIME ZONE statement, as in:

SELECT yourdto AT TIME ZONE @thetimezoneid

Otherwise, consider using my SQL Server Time Zone Support project.

Neither of which can give you the system's time zone ID. You must know which time zone you want to work with.

Upvotes: 1

Related Questions