title: Convert UTC on Azure to EST Time (including Daylight saving Time-dynamic version)
body: <p>I have a question about converting UTC time zone to EST time zone since I am still new to SQL language and Azure platform. In log file, I used system built-in function "GetDate()" in log file to get the Date/Time.  However, while accessing Azure database on my SSMS, using system-built in function (GetDate()) gives me the datetime in UTC time zone, which is 4 hours ahead of Eastern Time Zone (EST). I have asked similar question before for converting UTC to EST <a href="https://stackoverflow.com/questions/53027616/inline-table-valued-function-without-a-parameter">here</a>
  , and @DanGuzman helped me fix my code. But this question is more about converting UTC to EST (considering Daylight saving time dynamically). Below is my code so far, and I used <a href="https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement/52520927#52520927">this</a> link as a reference.  However, I would like to make my code dynamic so that I can keep using it for 2020, 2021 as well.</p>
  
  <p>Below code works ONLY for 2019 (since Daylight Saving starts on March 10,2019 until November 3, 2019. Within the date range, below code forward one hour of EST time during daylight saving time range. </p>
  
  <pre><code>CREATE FUNCTION [dbo].[EST_GetDateTime] 
      (
       -- no parameter
       )
  RETURNS datetime
  AS
  BEGIN
  
   DECLARE
      @EST datetime,  
      @StandardOffset int,
  
      @DST datetime,  -- Daylight Saving Time
      @SSM datetime, -- Second Sunday in March
      @FSN datetime  -- First Sunday in November
  
  -- get DST Range
  set @EST = CAST(DATEADD(hh,-5,GETDATE()) AS DATETIME)
  
  set @StandardOffset = 0
  
  set @SSM = datename(year,@EST) + '0310'  -- Set which day daylight saving start (for 2019, March 10)
  set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
  set @FSN = datename(year,@EST) + '1103'   -- Set which day daylight saving start (for 2019, March 10)
  set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))
  
  -- add an hour to @StandardOffset if @EST is in DST range
  if @EST between @SSM and @FSN
      set @StandardOffset = @StandardOffset + 1
  
  -- convert to DST
  set @EST = CAST(DATEADD(hh,-5+@StandardOffset ,GETDATE()) AS DATETIME)
  
      RETURN @EST
  END
  GO
  </code></pre>
  
  <p>Can someone please give any suggestions how to improve my existing code (able to work dynamically) so that I do not need to change the function every single year for adjusting day light saving.</p>