Azure SQL Database - How to change time zone settings?

Question

My Azure server is hosted in East US. In the SQL DB, when I use getdate(), it returns UTC time. But I need to get EST time. How can I achieve it? Is there any setting I need to change?

Answer 1

The easiest way to get the current time in the US Eastern time zone is:

select SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time'

This returns a datetimeoffset and accounts correctly for daylight saving time.

Answer 2

OP has developed a function:

FN_GET_EST(GETDATE()). 

CREATE FUNCTION FN_GET_EST(@p_in_date as datetime) returns DATETIME 
as 
begin 
DECLARE @dt_offset AS datetimeoffset 
SET @dt_offset = CONVERT(datetimeoffset, @p_in_date) AT TIME ZONE 'Eastern Standard Time' 
RETURN CONVERT(datetime, @dt_offset); 
end