SQL Server 2008 - 如何将 GMT(UTC) 日期时间转换为本地日期时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6064674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 - How to convert GMT(UTC) datetime to local datetime?
提问by slandau
I have an insert proc that passes in GETDATE()as one of the values because each insert also stores when it was inserted. This is hosted on SQL Azure - which uses GMT.
我有一个插入过程，它GETDATE()作为值之一传入，因为每个插入也存储它被插入的时间。这托管在 SQL Azure 上 - 它使用 GMT。
Now, when I am receiving messages, I have the GMT date stored for each of them in their timestamp columns, how do I convert this to the local datetimefor wherever you are when you are accessing my page?
现在，当我接收消息时，我将每个消息的 GMT 日期存储在它们的时间戳列中，datetime当您访问我的页面时，我如何将其转换为本地时间？
Thanks.
谢谢。
回答by Alex Aza
You could do something like this:
你可以这样做：
declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'
declare @LocalDateTime datetime2 = dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @InputUtcDateTime)
print @LocalDateTime
or
或者
declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'
declare @LocalDateTime datetime2 = dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), @InputUtcDateTime)
print @LocalDateTime
回答by bwperrin
Aside from the Daylight Savings issue, why not simplify with:
除了夏令时问题，为什么不简化：
yourDateTime - getutcdate() + getdate()
回答by Tracker1
For MST as an example... considering each DTM is stored in GMT already, that simplifies things..
以 MST 为例......考虑到每个 DTM 已经存储在 GMT 中，这简化了事情......
SWITCHOFFSET(CONVERT(DATETIMEOFFSET, [ColumnName]), '-07:00')
Now, if your local date/time is something other than GMT/UTC, you'll likely want to use the following...
现在，如果您的本地日期/时间不是 GMT/UTC，您可能需要使用以下...
SWITCHOFFSET(TODATETIMEOFFSET([ColumnName], datepart(tz,sysdatetimeoffset())),'+00:00')
Here's the breakdown.
这是细分。
SWITCHOFFSET- converts a DateTimeOffset value to a different timezone, while preserving the offset.TODATETIMEOFFSET- converts a DateTime value to a DateTimeOffset value at a specified timezone.DATEPART- in this case is getting the timezone part of the local datetime.'+00:00'- the target offset, in the second example is UTC/GMT target, from local... the former example is to MST.
SWITCHOFFSET- 将 DateTimeOffset 值转换为不同的时区，同时保留偏移量。TODATETIMEOFFSET- 将 DateTime 值转换为指定时区的 DateTimeOffset 值。DATEPART- 在这种情况下是获取本地日期时间的时区部分。'+00:00'- 目标偏移量，在第二个示例中是 UTC/GMT 目标，从本地...前一个示例是到 MST。
NOTE/WARNING: I don't believe that this accounts for Daylight Savings Time, which could be an issue for you. If absolute preservation isn't necessary, you may want to simply add a secondary column, with the rough conversion and go forward safely.
注意/警告：我认为这不是夏令时的原因，这对您来说可能是个问题。如果不需要绝对保留，您可能只想添加辅助列，进行粗略转换并安全前进。
You may want to abstract the logic into a function call, in order to account for the preservation of DST... it shouldn't be excessively difficult to do, though.
您可能希望将逻辑抽象到函数调用中，以便考虑到 DST 的保留……不过，这样做应该不会太难。
回答by Rich
Here's a function which works on historic data. I wrote it for British Summer time - which unfortunately occurs on the last Sunday of the months of March and October, making the logic a little convoluted.
这是一个处理历史数据的函数。我是为英国夏令时写的——不幸的是发生在 3 月和 10 月的最后一个星期日，这使得逻辑有点复杂。
Basically the hard coded date part 01/03 is looking for the last Sunday in March and 01/10 is looking for the last Sunday in October (which is when the clocks go forward and back here). NOTE: IF YOUR SERVER IS USING NATIVE US DATES REVERSE THESE TWO DATE PARTS TO 03/01 and 10/01!!!!
基本上，硬编码日期部分 01/03 正在寻找 3 月的最后一个星期日，而 01/10 正在寻找 10 月的最后一个星期日（时钟在这里向前和向后）。注意：如果您的服务器使用美国本土日期，请将这两个日期部分更改为 03/01 和 10/01！！！！
So you feed it a UTC date and it'll automatically work out whether an historic date is BST or GMT. Not the best thing to use on a big data set but it's a solution.
所以你给它一个 UTC 日期，它会自动计算出历史日期是 BST 还是 GMT。不是在大数据集上使用的最佳方法，但它是一种解决方案。
Run this script to create the function and call it inline in your select. SQL 2008 has a problem with user defined functions, it seems, it puts a redline under the code, but it still runs it as long as you use the dbo prefix (SELECT dbo.UTCConvert(yourdate) to run it)
运行此脚本以创建函数并在您的选择中内联调用它。SQL 2008 有一个用户定义函数的问题，它似乎在代码下放了一条红线，但只要你使用 dbo 前缀（SELECT dbo.UTCConvert(yourdate) 来运行它）它仍然会运行它
CREATE FUNCTION [dbo].[UTCConvert]
(
@p1 datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime
RETURN CASE
WHEN
@p1 >
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/03/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
AND
@p1<
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/10/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
THEN (DATEADD(HH, 1, @p1))
ELSE @p1
END
END
回答by High Plains Grifter
/*
=============================================
Author: Mark Griffiths
Create date: 29/05/2018
Description: BST runs from 02:00AM on the last Sunday of March to the same time on the last Sunday of October.
The Series of DATEDIFFs and DATEADDS below function as follows
1 ● Count the number of months there have been between the given date and start of computer time
2 ● Add that number of months to the end of the first month to get the end of the given month
3 ● Count the number of days there have been between the end of the given month and the first Saturday
4 ● Add that number of days to the calculated end of the given month
5 ● Add Two hours to that time as the clocks go back at 02:00 in the morning
I know that the tabbing below makes it all look odd, but the description above is the best way I could find to comment things, given the nesting...
The comments in the code below should help find the nesting levels and the numbers refer to the bullet points above.
=============================================
-- Test Variables --
DECLARE @GMTime DATETIME2(3) = '2018-05-01 12:00:00.000'
*/
DECLARE @RealTime As DATETIME2(3)
DECLARE @Year VARCHAR(4)
SET @Year = CONVERT(VARCHAR,DATEPART(YEAR,@GMTime))
DECLARE @StartOfBST AS DATETIME
DECLARE @EndOfBST AS DATETIME
SELECT
@StartOfBST =
DATEADD -----------------------------------------------------------------------------------------
( -- |
HOUR -- |
,2 -- |
,DATEADD ----------------------------------------------------------------------------- |
( -- | |
DAY -- | |
,DATEDIFF ------------------------------------------------------------- | |
( -- | | |
DAY -- | | |
,'19000107' -- | | 5
,DATEADD --------------------------------------------- | | |
( -- | 3 4 |
MONTH -- | | | |
,DATEDIFF(MONTH,0,CONVERT(DATE,'03/01/' + @Year)) -- 1 2 | | |
,CONVERT(DATE,'01/31/1900') -- | | | |
) --------------------------------------------- | | |
)/7*7 ------------------------------------------------------------- | |
,'19000107' -- | |
) --------------------------------------------------------------------- |
), -----------------------------------------------------------------------------------------
@EndOfBST =
DATEADD(HOUR,2,DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,CONVERT(DATE,'10/01/' + @Year)),30))/7*7,'19000107'))
SET @RealTime = CASE
WHEN @GMTime BETWEEN @StartOfBST AND @EndOfBST THEN DATEADD(HOUR,-1,@GMTime)
ELSE @GMTime
END
RETURN @RealTime;
--SELECT @RealTime
END
*EDIT: Changed CONVERT(DATE,'01/30/1900')to CONVERT(DATE,'01/31/1900')as since 45BC, January has had 31 days. This caused some times an incorrect result for 2019, and any others where the last Sunday in March is the 31st.
*编辑：改变CONVERT(DATE,'01/30/1900')以CONVERT(DATE,'01/31/1900')作为自45BC，一月已经有31天。这有时会导致 2019 年的结果不正确，以及 3 月的最后一个星期日是 31 日的任何其他结果。
