SQL 将 getdate() 转换为 EST
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4712616/
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
Convert getdate() to EST
提问by derin
I would like to convert getdate()in SQL Server to EST time.
我想getdate()在 SQL Server 中转换为 EST 时间。
回答by RichardTheKiwi
Have you considered GETUTCDATE() and performing the offset from there? If you mean EST as in standard time, then that is UTC-5, so
您是否考虑过 GETUTCDATE() 并从那里执行偏移量？如果您的意思是标准时间中的 EST，那么就是 UTC-5，所以
select dateadd(hour,-5,GETUTCDATE())
回答by Troy Witthoeft
UPDATED ANSWER (05-29-2020)
更新答案 (05-29-2020)
The Azure SQL team has released a new functionwhich makes this even easier. SELECT CURRENT_TIMEZONE()will return your server's timezone. Adding this function into the ORIGINAL ANSWER below yields a single query will work globally on all Azure SQL Servers.
Azure SQL 团队发布了一个新功能，使这变得更加容易。 SELECT CURRENT_TIMEZONE()将返回您服务器的时区。将此函数添加到下面的 ORIGINAL ANSWER 中会生成一个查询，它将在所有 Azure SQL Server 上全局运行。
SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE (SELECT CURRENT_TIMEZONE_ID()) AT TIME ZONE 'Eastern Standard Time')
SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE (SELECT CURRENT_TIMEZONE_ID()) AT TIME ZONE 'Eastern Standard Time')
This query will work on any Azure SQL Server.
此查询适用于任何 Azure SQL Server。
ORIGINAL ANSWER:
原始答案：
There are a lot of answers here that are unnecessarily complex, or that don't account for daylight savings time. As of SQL Server 2016, converting between timezones can be done with a single lineof native sql.
这里有很多答案不必要地复杂，或者没有考虑到夏令时。从 SQL Server 2016 开始，时区之间的转换可以通过一行原生 sql来完成。
SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')
Above, we are using the AT TIME ZONEfeatures, described in more detail here. It's just a query. It can be called from reports or used on databases that are read-only.
上面，我们正在使用这些AT TIME ZONE功能，这里有更详细的描述。这只是一个查询。它可以从报告中调用或用于只读数据库。
There may be some functions and features that are new here, so an explanation is warranted. The query above calls GETDATE()and sets it's timezone as UTC using AT TIMEZONE. Implicitly, this is also changing it's datatype from a datetimeto datetimeoffset. Next, we'll call AT TIMEZONEagain to cut it over to EST. Lastly, we'll wrap the entire thing in CONVERT()to get it back to a datetime, dropping the unneeded +/- hours portion during the process.
这里可能有一些新的功能和特性，所以有必要解释一下。上面的查询调用GETDATE()并将其时区设置为 UTC 使用AT TIMEZONE. 隐含地，这也将其数据类型从 a 更改datetime为datetimeoffset。接下来，我们将AT TIMEZONE再次调用将其切换到 EST。最后，我们将把整个东西包装起来，CONVERT()让它回到一个datetime，在这个过程中去掉不需要的 +/- 小时部分。
Taking the query step-by-step ...
逐步进行查询...
SELECT [GetDate] = GETDATE()
SELECT [GetDateAtUtc] = GETDATE() AT TIME ZONE 'UTC'
SELECT [GetDateAtUtcAtEst] = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'
SELECT [GetDateEst] = CONVERT(DATETIME,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')
回答by AlexGirenko
SQL server itself has the table current_utc_offsetwith correct offset for summer and winter time.
Please, try the query select * from current_utc_offset, change the date to different season on your server and review the table again.
So the correct function to get EST would be:
SQL Server 本身具有current_utc_offset夏季和冬季时间具有正确偏移量的表。请尝试查询select * from current_utc_offset，在您的服务器上将日期更改为不同的季节，然后再次查看表格。所以获得 EST 的正确函数是：
CREATE FUNCTION [dbo].[Getestlocaldatetime] ()
returns DATETIME
AS
BEGIN
DECLARE @zz NVARCHAR(12);
DECLARE @hh NVARCHAR(3);
DECLARE @dd DATETIME;
SET @zz = (SELECT current_utc_offset
FROM sys.time_zone_info
WHERE NAME = N'US Eastern Standard Time')
SET @hh = Substring(@zz, 1, 3);
SET @dd = Dateadd(hh, CONVERT(INT, @hh), Getutcdate())
RETURN @dd
END
回答by NishantJ
EST is GMT-5 hours while EDT is GMT-4 hours.
EST 是 GMT-5 小时，而 EDT 是 GMT-4 小时。
To get EST:
获得 EST：
select dateadd(hour,-5,GETUTCDATE())
To get EDT :
要获得 EDT ：
select dateadd(hour,-4,GETUTCDATE())
回答by Brian Smith
SELECT CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 100) AS [Date_Result]
回答by Alex Fenech
If you want to do this without calling a function, you can do it with a CASE statement as well. The code below converts a UTC field to Mountain Time accounting for daylight savings. For EST, you would just change all the -6 to -4 and change all the -7 to -5.
如果您想在不调用函数的情况下执行此操作，也可以使用 CASE 语句来执行此操作。下面的代码将 UTC 字段转换为山地时间以考虑夏令时。对于 EST，您只需将所有 -6 更改为 -4，并将所有 -7 更改为 -5。
--Adjust a UTC value, in the example the UTC field is identified as UTC.Field, to account for daylight savings time when converting out of UTC to Mountain time.
CASE
--When it's between March and November, it is summer time which is -6 from UTC
WHEN MONTH ( UTC.Field ) > 3 AND MONTH ( UTC.Field ) < 11
THEN DATEADD ( HOUR , -6 , UTC.Field )
--When its March and the day is greater than the 14, you know it's summer (-6)
WHEN MONTH ( UTC.Field ) = 3
AND DATEPART ( DAY , UTC.Field ) >= 14
THEN
--However, if UTC is before 9am on that Sunday, then it's before 2am Mountain which means it's still Winter daylight time.
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '9:00'
--Before 2am mountain time so it's winter, -7 hours for Winter daylight time
THEN DATEADD ( HOUR , -7 , UTC.Field )
--Otherwise -6 because it'll be after 2am making it Summer daylight time
ELSE DATEADD ( HOUR , -6 , UTC.Field )
END
WHEN MONTH ( UTC.Field ) = 3
AND ( DATEPART ( WEEKDAY , UTC.Field ) + 7 ) <= DATEPART ( day , UTC.Field )
THEN
--According to the date, it's moved onto Summer daylight, but we need to account for the hours leading up to 2am if it's Sunday
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '9:00'
--Before 9am UTC is before 2am Mountain so it's winter Daylight, -7 hours
THEN DATEADD ( HOUR , -7 , UTC.Field )
--Otherwise, it's summer daylight, -6 hours
ELSE DATEADD ( HOUR , -6 , UTC.Field )
END
--When it's November and the weekday is greater than the calendar date, it's still Summer so -6 from the time
WHEN MONTH ( UTC.Field ) = 11
AND DATEPART ( WEEKDAY , UTC.Field ) > DATEPART ( DAY , UTC.Field )
THEN DATEADD ( HOUR , -6 , UTC.Field )
WHEN MONTH ( UTC.Field ) = 11
AND DATEPART ( WEEKDAY , UTC.Field ) <= DATEPART ( DAY , UTC.Field )
--If the weekday is less than or equal to the calendar day it's Winter daylight but we need to account for the hours leading up to 2am.
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '8:00'
--If it's before 8am UTC and it's Sunday in the logic outlined, then it's still Summer daylight, -6 hours
THEN DATEADD ( HOUR , -6 , UTC.Field )
--Otherwise, adjust for Winter daylight at -7
ELSE DATEADD ( HOUR , -7 , UTC.Field )
END
--If the date doesn't fall into any of the above logic, it's Winter daylight, -7
ELSE
DATEADD ( HOUR , -7 , UTC.Field )
END
回答by Jin Thakur
For those who are using latest version of sql server can create a .net function
对于那些使用最新版本的 sql server 的人可以创建一个 .net 函数
A scalar-valued function (SVF) returns a single value, such as a string, integer, or bit value. You can create scalar-valued user-defined functions in managed code using any .NET Framework programming language. These functions are accessible to Transact-SQL or other managed code. For information about the advantages of CLR integration and choosing between managed code and Transact-SQL.
标量值函数 (SVF) 返回单个值，例如字符串、整数或位值。您可以使用任何 .NET Framework 编程语言在托管代码中创建标量值用户定义函数。Transact-SQL 或其他托管代码可以访问这些函数。有关 CLR 集成的优势以及在托管代码和 Transact-SQL 之间进行选择的信息。
Since .NET has access to all time zone at operating system so you don't have to calculate daylight saving -4 or -5 fundamentals.
由于 .NET 可以访问操作系统的所有时区，因此您不必计算夏令时 -4 或 -5 基础。
var timeUtc = DateTime.UtcNow;
TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime easternTime = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, easternZone);
回答by Laxmi
using GETDATE() :
使用 GETDATE() ：
GMT :
格林威治标准时间 ：
DATE WILL BE DISPLAYED:
显示日期：
SELECT GETDATE()
EST :
美东时间 ：
USE GETDATE() FOR CONVERSION NOW:
现在使用 GETDATE() 进行转换：
SELECT DATEADD(HOUR, -4, CONVERT(varchar(20),GETDATE(),120))
回答by Empiricist
If you are attempting to output a local time, such as eastern time, with Daylight savings time, you need a function that Detects the start and end of daylight savings time and then applies a variable offset: I've found this: http://joeyiodice.com/convert-sql-azure-getdate-utc-time-to-local-time/useful.
如果您尝试使用夏令时输出当地时间，例如东部时间，则需要一个检测夏令时开始和结束的函数，然后应用可变偏移量：我发现了这个：http:/ /joeyiodice.com/convert-sql-azure-getdate-utc-time-to-local-time/很有用。
回答by AndroidDebaser
GetDate()is the system time from the server itself.
GetDate()是来自服务器本身的系统时间。
Take the hour difference of the GetDate()now and the time it is now in EST use this code where 1 is that said difference ( in this instance the server is in Central Time zone) (This is also assuming your server is accounting for DST)
以GetDate()现在和现在在 EST 中的时间的小时差使用此代码，其中 1 是所说的差异（在这种情况下，服务器处于中央时区）（这也假设您的服务器正在考虑 DST）
SELECT Dateadd(hour, 1, Getdate()) AS EST
