在 SSMS 中，以本地时区显示 DateTimeOffset 值 [英] In SSMS, display DateTimeOffset value in local timezone

问题描述

有没有办法配置 Sql Server Management Studio 在查询结果中显示客户端本地时区中的 DateTimeOffset 列的值，而不是 UTC?

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?

示例:我在时区 +02:00.一个值存储为 2016-07-27 22:00:00.0000000 +00:00.目前，该值在查询结果中显示为这样.我希望在我的机器上在 SSMS 上执行此查询时将其显示(格式化)为 2016-07-28 00:00:00.0000000 +02:00.

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.

目前我正在手动使用类似 SWITCHOFFSET(CONVERT(datetimeoffset, <DateColumn>), DATENAME(TzOffset, SYSDATETIMEOFFSET())) 之类的东西，这显然非常麻烦.

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

如果我没记错的话，在 Oracle 中，会话级别的 NLS 参数可以用于此目的.Sql Server 有没有类似的东西?

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?

推荐答案

SQL Server 和 SSMS 都无法处理会话时区"的概念(如 Oracle、MySql 等).

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

此外，无论如何自动转换偏移量是没有意义的，因为偏移量实际上是存储的 datetimeoffset 值的一部分.

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

如果您使用的是 SQL Server 2016 或 Azure SQL 数据库，则可以使用新的 AT TIME ZONE 语句，如:

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

否则，请考虑使用我的 SQL Server 时区支持 项目.

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

两者都不能为您提供系统的时区 ID.您必须知道要使用哪个时区.

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

这篇关于在 SSMS 中，以本地时区显示 DateTimeOffset 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！