title: UTC time to Local Time Zone(Central Time) Conversion MS SQL Sever
body: <p>This question is a follow up from this <a href="https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement">question</a>. 
  I have a UTC time column and I want convert to into current local time <code>(Central Time Zone or America/Chicago)</code>. I tried to use the function from @Ron Smith's answer, which is <code>[dbo].[fn_UTC_to_DST]</code> function. </p>
  
  <p>In that function, it needs two arguments such as UTC time and offset. I input both like this, </p>
  
  <pre><code>SELECT  dbo.fn_UTC_to_DST([time(UTC)],5) as Date
  FROM tbl
  </code></pre>
  
  <p>Since we are in Day Light Saving time, I am using 5 as my offset. My output looks like this,</p>
  
  <pre><code>2017-09-27 20:55:00.000
  2017-09-27 20:56:00.000
  2017-09-27 20:57:00.000
  2017-09-27 20:58:00.000
  ...
  </code></pre>
  
  <p>Which should be (central time),</p>
  
  <pre><code>2017-09-27 09:55:00.000
  2017-09-27 09:56:00.000
  2017-09-27 09:57:00.000
  2017-09-27 09:58:00.000
  ...
  </code></pre>
  
  <p>So, I changed @Ron Smith's function like this,</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fn_UTC_to_DST]
  (
      @UTC datetime,
      @StandardOffset int
  )
  RETURNS datetime
  AS
  BEGIN
  declare 
      @DST datetime,
      @SSM datetime, -- Second Sunday in March
      @FSN datetime  -- First Sunday in November
  
  -- get DST Range
  set @SSM = datename(year,@UTC) + '0314' 
  set @SSM = dateadd(hour,-5,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))  -- Changed from 2 to -5
  set @FSN = datename(year,@UTC) + '1107'
  set @FSN = dateadd(second,-6,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))  -- changed from 1 to -6 
  
  -- add an hour to @StandardOffset if @UTC is in DST range
  if @UTC between @SSM and @FSN
      set @StandardOffset = @StandardOffset + 1
  
  -- convert to DST
  set @DST = dateadd(hour,@StandardOffset,@UTC)
  
  -- return converted datetime
  return @DST
  
  END
  
  GO
  </code></pre>
  
  <p>However, this still gives me the same result as above. 
  1. What should I change for Central time?
  2. Is there a way to automatically change to -5 during daylight saving time and -6 during standard time?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>After looking at the Answer and the <a href="http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/" rel="nofollow noreferrer">reference link</a> from #Siyual,I created the <code>dbo.TZCalendar</code> table and I tried to create a function like this (takes one argument and returns a date from refrence link)</p>
  
  <pre><code>CREATE FUNCTION dbo.ConvertUTCToLocal
  (
    @utc DATETIME
  )
  RETURNS Datetime
  AS BEGIN 
    SELECT UTCToLocal = DATEADD(HOUR, CASE 
  
    -- within Daylight Savings Time
    WHEN @utc &gt;= UTC_DST_Start AND @utc &lt; UTC_DST_End 
    THEN -5 
  
    -- within Standard Time
    ELSE -6 END, @utc)
  
  FROM dbo.TZCalendar 
  WHERE CONVERT(DATE,@utc) &gt;= [Year] 
    AND CONVERT(DATE,@utc) &lt; DATEADD(YEAR, 1, [Year])
  END
  GO
  </code></pre>
  
  <p>This, does not work. The logic seems right for me, but, I just need a function without <code>SCHEMABINDING</code> (which is done in the reference link). How can I do that?</p>