Convert getdate() to EST

Question

I would like to convert getdate() in SQL Server to EST time.

Solution 1

UPDATED ANSWER (05-29-2020)

The Azure SQL team has released a new function which makes this even easier. SELECT CURRENT_TIMEZONE_ID() 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.

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.

ORIGINAL ANSWER:

There are a lot of answers here that are unnecessarily complex, or that don't account for daylight savings time. No massive CASE statements needed. No new stored procedure, or scalar/user defined functions are needed. As of SQL Server 2016, converting between timezones can be done with a single line of native sql. This has advantages. For example, it can be called from reports or used on databases that are read-only.

SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')

That's it. Above, we are using the AT TIME ZONE features, described in more detail here. 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 datetime to datetimeoffset. Next, we'll call AT TIMEZONE again 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.

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')

Solution 2

Have you considered GETUTCDATE() and performing the offset from there? If you mean EST as in standard time, then that is UTC-5, so

select dateadd(hour,-5,GETUTCDATE())

Solution 3

EST is GMT-5 hours while EDT is GMT-4 hours.

To get EST:

select dateadd(hour,-5,GETUTCDATE())

To get EDT :

select dateadd(hour,-4,GETUTCDATE())

Solution 4

SQL server itself has the table current_utc_offset with 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:

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 

Solution 5

SELECT CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 100) AS [Date_Result]

https://stackoverflow.com/a/49449695/6559330

View more solutions

Comments

[AndroidDebaser]

WRONG, this is not accounting for day light saving time.

[solartic]

@AndroidDebaser You don't need to account for day light saving time. EST will always be UTC-5. Places that observer day light saving time will switch to EDT and the OP asked for EST specifically. So this answer is correct.

[Ross Bush]

That is subject to change. There is nothing requiring the offset to be constant.

[Ross Bush]

These are the same values that are contained in time zone info registry keys which is what windows uses to keep itself in sync. This is the best answer for SQL Server 2014 and up.

[Troy Witthoeft]

I think it might be best for 2014, but not 2016 and up. SQL 2016 introuduced the AT TIME ZONE feature which simplifies this. That feature is also using the sys.time_zone_info table.

[Troy Witthoeft]

@Solartic Sure, this answer is technically correct because EST is canoncially always five out from UTC. However, the OP most likely wants a forumula that works year round. I say fair, because folks call it EST year round. There is no entry for EDT sys.time_zone_info table. So while this answer is technically correct, I think It's fair to presume the OP wants something that accepts a UTC date and translate it correctly to their current eastern time, whether recgonizing daylight savings or not. For that reason, I believe the other answers here are more useful. Thanks.

[Robert Sawyer]

I think this is the most eloquent, I discovered you need to get the UTC at timezone UTC before you convert to Eastern Standard Time, otherwise you will get thrown off by whatever the server timezone is.

[Simon.S.A.]

Very nice. Note that you can edit your answers so next time you do not have to comment on it.

[laancelot]

Updated, well explained, well demonstrated... I like everything about this answer.

[solartic]

@troy-witthoeft I do think it is fair to take it into consideration that the OP might want Eastern Time Zone (ET) instead of EST. But not fair to assume they don't specifically need EST. I think this is a good answer for what was asked. Is it really what the OP wants? Maybe not. And that's the value of having other answers. Together they all add value. Some short, simple and to the point and some with useful potential considerations. Thanks for your response.

[JayTee]

The GetDateEst (4th line down) just saved me from writing a messy function to change '-5:00' into '-5' for purposes of use in a DATEADD function. Here's an upvote! ⬆