在 select 语句中将 Datetime 列从 UTC 转换为本地时间 [英] Convert Datetime column from UTC to local time in select statement

问题描述

我正在执行一些 SQL 选择查询，并希望将我的 UTC 日期时间列转换为本地时间，以便在我的查询结果中显示为本地时间.请注意，我不希望通过代码进行此转换，而是在对我的数据库进行手动和随机 SQL 查询时.

I'm doing a few SQL select queries and would like to convert my UTC datetime column into local time to be displayed as local time in my query results. Note, I am NOT looking to do this conversion via code but rather when I am doing manual and random SQL queries against my databases.

推荐答案

您可以在 SQL Server 2008 或更高版本上执行以下操作:

You can do this as follows on SQL Server 2008 or greater:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

你也可以不那么冗长:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

无论你做什么，不要使用 - 减去日期，因为该操作不是原子的，并且由于在系统日期时间和本地日期时间在不同时间检查(即非原子地).

Whatever you do, do not use - to subtract dates, because the operation is not atomic, and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times (i.e., non-atomically).

请注意，此答案未考虑夏令时.如果您想包含 DST 调整，另请参阅以下 SO 问题:

Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:

如何创建SQL Server 中的夏令时开始和结束函数

这篇关于在 select 语句中将 Datetime 列从 UTC 转换为本地时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！