<p>What is the best way to convert a UTC datetime into local datetime. It isn't as simple as a getutcdate() and getdate() difference because the difference changes depending on what the date is.</p>

<p>CLR integration isn't an option for me either.</p>

<p>The solution that I had come up with for this problem a few months back was to have a daylight savings time table that stored the beginning and ending daylight savings days for the next 100 or so years, this solution seemed inelegant but conversions were quick (simple table lookup)</p>

[{'answer_id': 25032, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 0, 'selected': False, 'text': "<p>Maintain a TimeZone table, or shell out with an extended stored proc (xp_cmdshell or a COM component, or your own) and ask the OS to do it. If you go the xp route, you'd probably want to cache the offset for a day.</p>\n"}, {'answer_id': 25073, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 6, 'selected': True, 'text': "<p>Create two tables and then join to them to convert stored GMT dates to local time:</p>\n\n<pre><code>TimeZones     e.g.\n---------     ----\nTimeZoneId    19\nName          Eastern (GMT -5)\nOffset        -5\n</code></pre>\n\n<p>Create the daylight savings table and populate it with as much information as you can (local laws change all the time so there's no way to predict what the data will look like years in the future)</p>\n\n<pre><code>DaylightSavings\n---------------\nTimeZoneId    19\nBeginDst      3/9/2008 2:00 AM\nEndDst        11/2/2008 2:00 AM\n</code></pre>\n\n<p>Join them like this:</p>\n\n<pre><code>inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId\nleft join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone \n    and x.TheDateToConvert between ds.BeginDst and ds.EndDst\n</code></pre>\n\n<p>Convert dates like this:</p>\n\n<pre><code>dateadd(hh, tz.Offset + \n    case when ds.LocalTimeZone is not null \n    then 1 else 0 end, TheDateToConvert)\n</code></pre>\n"}, {'answer_id': 25328, 'author': 'Brad Wilson', 'author_id': 1554, 'author_profile': 'https://Stackoverflow.com/users/1554', 'pm_score': 3, 'selected': False, 'text': '<p>If either of these issues affects you, you should never store local times in the database:</p>\n\n<ol>\n<li>With DST is that there is an "hour of uncertainty" around the falling back period where a local time cannot be unambiguously converted. If exact dates &amp; times are required, then store in UTC.</li>\n<li>If you want to show users the date &amp; time in their own timezone, rather than the timezone in which the action took place, store in UTC.</li>\n</ol>\n'}, {'answer_id': 838184, 'author': 'Bob Albright', 'author_id': 15050, 'author_profile': 'https://Stackoverflow.com/users/15050', 'pm_score': 4, 'selected': False, 'text': "<p>If you're in the US and only interested in going from UTC/GMT to a fixed time zone (such as EDT) this code should suffice.  I whipped it up today and believe it's correct but use at your own risk.</p>\n\n<p>Adds a computed column to a table 'myTable'  assuming your dates are on the 'date' column.  Hope someone else finds this useful.</p>\n\n<pre><code>ALTER TABLE myTable ADD date_edt AS \n  dateadd(hh, \n        -- The schedule through 2006 in the United States was that DST began on the first Sunday in April \n        -- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006). \n        -- The time is adjusted at 02:00 local time.\n              CASE WHEN YEAR(date) &lt;= 2006 THEN  \n                    CASE WHEN \n                              date &gt;=  '4/' + CAST(abs(8-DATEPART(dw,'4/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' \n                          AND \n                              date &lt; '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date) as varchar)) as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' \n                    THEN -4 ELSE -5 END\n              ELSE\n        -- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007. \n        -- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on \n        -- the first Sunday of November, one week later than in years past. This change resulted in a new DST period \n        -- that is four weeks (five in years when March has five Sundays) longer than in previous years.[35] In 2008 \n        -- daylight saving time ended at 02:00 on Sunday, November 2, and in 2009 it began at 02:00 on Sunday, March 8.[36]\n                    CASE WHEN \n                              date &gt;= '3/' + CAST(abs(8-DATEPART(dw,'3/1/' + CAST(YEAR(date) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' \n                          AND \n                              date &lt; \n                                '11/' + CAST(abs(8-DATEPART(dw,'11/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' \n                    THEN -4 ELSE -5 END\n              END\n  ,date)\n</code></pre>\n"}, {'answer_id': 4035662, 'author': 'Larry', 'author_id': 489129, 'author_profile': 'https://Stackoverflow.com/users/489129', 'pm_score': 3, 'selected': False, 'text': '<p>In <a href="https://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005/25073#25073">Eric Z Beard\'s answer</a>, the following SQL</p>\n\n<pre><code>inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId \nleft join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone  \n    and x.TheDateToConvert between ds.BeginDst and ds.EndDst \n</code></pre>\n\n<p>might more accurately be:</p>\n\n<pre><code>inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId \nleft join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone  \n    and x.TheDateToConvert &gt;= ds.BeginDst and x.TheDateToConvert &lt; ds.EndDst \n</code></pre>\n\n<p>(above code not tested)</p>\n\n<p>The reason for this is that the sql "between" statement is inclusive.  On the back-end of DST, this would result in a 2AM time NOT being converted to 1AM.   Of course the likelihood of the time being 2AM precisely is small, but it can happen, and it would result in an invalid conversion.</p>\n'}, {'answer_id': 16994483, 'author': 'WillDeStijl', 'author_id': 1241580, 'author_profile': 'https://Stackoverflow.com/users/1241580', 'pm_score': 3, 'selected': False, 'text': '<p><strong>FOR READ-ONLY</strong> Use this(inspired by <a href="https://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005/838184#838184">Bob Albright\'s incorrect solution</a> ):</p>\n\n<pre><code>SELECT\n  date1, \n  dateadd(hh,\n    -- The schedule through 2006 in the United States was that DST began on the first Sunday in April \n    -- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006). \n    -- The time is adjusted at 02:00 local time (which, for edt, is 07:00 UTC at the start, and 06:00 GMT at the end).\n    CASE WHEN YEAR(date1) &lt;= 2006 THEN\n         CASE WHEN \n                  date1 &gt;=  \'4/\' + CAST((8-DATEPART(dw,\'4/1/\' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) +  \'/\' + CAST(YEAR(date1) as varchar) + \' 7:00\' \n                AND \n                  date1 &lt; \'10/\' + CAST(32-DATEPART(dw,\'10/31/\' + CAST(YEAR(date1) as varchar)) as varchar) +  \'/\' + CAST(YEAR(date1) as varchar) + \' 6:00\' \n              THEN -4 ELSE -5 END\n    ELSE\n        -- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007. \n        -- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on \n        -- the first Sunday of November, one week later than in years past. This change resulted in a new DST period \n        -- that is four weeks (five in years when March has five Sundays) longer than in previous years. In 2008 \n        -- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8\n        CASE WHEN \n                 date1 &gt;= \'3/\' + CAST((8-DATEPART(dw,\'3/1/\' + CAST(YEAR(date1) as varchar)))%7 + 8 as varchar) +  \'/\' + CAST(YEAR(date1) as varchar) + \' 7:00\' \n               AND \n                 date1 &lt; \'11/\' + CAST((8-DATEPART(dw,\'11/1/\' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) +  \'/\' + CAST(YEAR(date1) as varchar) + \' 6:00\' \n             THEN -4 ELSE -5 END\n    END\n   , date1) as date1Edt\n  from MyTbl\n</code></pre>\n\n<p>I posted this answer after I tried to edit <a href="https://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005/838184#838184">Bob Albright\'s wrong answer</a>. I corrected the times and removed superfluous abs(), but my edits were rejected multiple times. I tried explaining, but was dismissed as a noob. His is a GREAT approach to the problem! It got me started in the right direction. I hate to create this separate answer when his just needs a minor tweak, but I tried ¯\\_(ツ)_/¯</p>\n'}, {'answer_id': 17820648, 'author': 'Daniel', 'author_id': 2612179, 'author_profile': 'https://Stackoverflow.com/users/2612179', 'pm_score': 3, 'selected': False, 'text': '<p>A much simpler and generic solution that considers daylight savings. Given an UTC date in "YourDateHere":</p>\n\n<pre><code>--Use Minutes ("MI") here instead of hours because sometimes\n--  the UTC offset may be half an hour (e.g. 9.5 hours).\nSELECT DATEADD(MI,\n               DATEDIFF(MI, SYSUTCDATETIME(),SYSDATETIME()),\n               YourUtcDateHere)[LocalDateTime]\n</code></pre>\n'}, {'answer_id': 22842711, 'author': 'MattC', 'author_id': 195198, 'author_profile': 'https://Stackoverflow.com/users/195198', 'pm_score': 0, 'selected': False, 'text': '<p>I like the answer <a href="https://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005/25073#25073">@Eric Z Beard</a> provided.  </p>\n\n<p>However, to avoid performing a join everytime, what about this?</p>\n\n<pre><code>TimeZoneOffsets\n---------------\nTimeZoneId    19\nBegin         1/4/2008 2:00 AM\nEnd           1/9/2008 2:00 AM\nOffset        -5\nTimeZoneId    19\nBegin         1/9/2008 2:00 AM\nEnd           1/4/2009 2:00 AM\nOffset        -6\nTimeZoneId    20 --Hong Kong for example - no DST\nBegin         1/1/1900\nEnd           31/12/9999\nOffset        +8\n</code></pre>\n\n<p>Then</p>\n\n<pre><code> Declare @offset INT = (Select IsNull(tz.Offset,0) from YourTable ds\n join   TimeZoneOffsets tz on tz.TimeZoneId=ds.LocalTimeZoneId  \n and x.TheDateToConvert &gt;= ds.Begin and x.TheDateToConvert &lt; ds.End)\n</code></pre>\n\n<p>finally becoming</p>\n\n<pre><code> dateadd(hh, @offset, TheDateToConvert)\n</code></pre>\n'}, {'answer_id': 36869547, 'author': 'Milan', 'author_id': 1438675, 'author_profile': 'https://Stackoverflow.com/users/1438675', 'pm_score': 0, 'selected': False, 'text': '<p>I\'ve read through a lot of StackOverflow posts in regards to this issue and found many methods. Some "sort of" ok. I also found this MS reference (<a href="https://msdn.microsoft.com/en-us/library/mt612795.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/mt612795.aspx</a>) which I tried to utilize in my script. I have managed to achieve the required result BUT I am not sure if this will run on 2005 version. Either way, I hope this helps.</p>\n\n<h1>Fnc to return PST from the system UTC default</h1>\n\n<pre><code>CREATE FUNCTION dbo.GetPst()\nRETURNS DATETIME\nAS \nBEGIN\n\n    RETURN  SYSDATETIMEOFFSET() AT TIME ZONE \'Pacific Standard Time\'\n\nEND\n\nSELECT dbo.GetPst()\n</code></pre>\n\n<h1>Fnc to return PST from the provided timestamp</h1>\n\n<pre><code>CREATE FUNCTION dbo.ConvertUtcToPst(@utcTime DATETIME)\nRETURNS DATETIME\nAS\nBEGIN\n\n    RETURN DATEADD(HOUR, 0 - DATEDIFF(HOUR, CAST(SYSDATETIMEOFFSET() AT TIME ZONE \'Pacific Standard Time\' AS DATETIME), SYSDATETIME()), @utcTime)\n\nEND\n\n\nSELECT dbo.ConvertUtcToPst(\'2016-04-25 22:50:01.900\')\n</code></pre>\n'}, {'answer_id': 38960682, 'author': 'Jody Wood', 'author_id': 6718940, 'author_profile': 'https://Stackoverflow.com/users/6718940', 'pm_score': 0, 'selected': False, 'text': "<p>I am using this because all of my dates are from now forward.</p>\n\n<pre><code>DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, GETDATE())\n</code></pre>\n\n<p>For historical dates (or to handle future changes in DST, I'm guessing Bob Albright's solution would be the way to go.</p>\n\n<p>The modification I make to my code is to use the target column:</p>\n\n<pre><code>DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, [MySourceColumn])\n</code></pre>\n\n<p>So far, this seems to work, but I'm happy to receive feedback.</p>\n"}, {'answer_id': 41905349, 'author': 'mattmc3', 'author_id': 83144, 'author_profile': 'https://Stackoverflow.com/users/83144', 'pm_score': 0, 'selected': False, 'text': "<p>Here is the code I use to make my timezone table. It's a bit naive, but is usually good enough.</p>\n\n<p>Assumptions:</p>\n\n<ol>\n<li>It assumes US only rules (DST is 2AM on some pre-defined Sunday,\netc).</li>\n<li>It assumes you don't have dates prior to 1970</li>\n<li>It assumes you know the local timezone offsets (i.e.: EST=-05:00, EDT=-04:00, etc.)</li>\n</ol>\n\n<p>Here's the SQL:</p>\n\n<pre><code>-- make a table (#dst) of years 1970-2101. Note that DST could change in the future and\n-- everything was all custom and jacked before 1970 in the US.\ndeclare @first_year varchar(4) = '1970'\ndeclare @last_year varchar(4) = '2101'\n\n-- make a table of all the years desired\nif object_id('tempdb..#years') is not null drop table #years\n;with cte as (\n    select cast(@first_year as int) as int_year\n          ,@first_year as str_year\n          ,cast(@first_year + '-01-01' as datetime) as start_of_year\n    union all\n    select int_year + 1\n          ,cast(int_year + 1 as varchar(4))\n          ,dateadd(year, 1, start_of_year)\n    from cte\n    where int_year + 1 &lt;= @last_year\n)\nselect *\ninto #years\nfrom cte\noption (maxrecursion 500);\n\n-- make a staging table of all the important DST dates each year\nif object_id('tempdb..#dst_stage') is not null drop table #dst_stage\nselect dst_date\n      ,time_period\n      ,int_year\n      ,row_number() over (order by dst_date) as ordinal\ninto #dst_stage\nfrom (\n    -- start of year\n    select y.start_of_year as dst_date\n          ,'start of year' as time_period\n          ,int_year\n    from #years y\n\n    union all\n    select dateadd(year, 1, y.start_of_year)\n          ,'start of year' as time_period\n          ,int_year\n    from #years y\n    where y.str_year = @last_year\n\n    -- start of dst\n    union all\n    select\n        case\n            when y.int_year &gt;= 2007 then\n                -- second sunday in march\n                dateadd(day, ((7 - datepart(weekday, y.str_year + '-03-08')) + 1) % 7, y.str_year + '-03-08')\n            when y.int_year between 1987 and 2006 then\n                -- first sunday in april\n                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-01')) + 1) % 7, y.str_year + '-04-01')\n            when y.int_year = 1974 then\n                -- special case\n                cast('1974-01-06' as datetime)\n            when y.int_year = 1975 then\n                -- special case\n                cast('1975-02-23' as datetime)\n            else\n                -- last sunday in april\n                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-24')) + 1) % 7, y.str_year + '-04-24')\n        end\n        ,'start of dst' as time_period\n        ,int_year\n    from #years y\n\n    -- end of dst\n    union all\n    select\n        case\n            when y.int_year &gt;= 2007 then\n                -- first sunday in november\n                dateadd(day, ((7 - datepart(weekday, y.str_year + '-11-01')) + 1) % 7, y.str_year + '-11-01')\n            else\n                -- last sunday in october\n                dateadd(day, ((7 - datepart(weekday, y.str_year + '-10-25')) + 1) % 7, y.str_year + '-10-25')\n        end\n        ,'end of dst' as time_period\n        ,int_year\n    from #years y\n) y\norder by 1\n\n-- assemble a final table\nif object_id('tempdb..#dst') is not null drop table #dst\nselect a.dst_date +\n          case\n             when a.time_period = 'start of dst' then ' 03:00'\n             when a.time_period = 'end of dst' then ' 02:00'\n             else ' 00:00'\n          end as start_date\n      ,b.dst_date +\n          case\n             when b.time_period = 'start of dst' then ' 02:00'\n             when b.time_period = 'end of dst' then ' 01:00'\n             else ' 00:00'\n          end as end_date\n      ,cast(case when a.time_period = 'start of dst' then 1 else 0 end as bit) as is_dst\n      ,cast(0 as bit) as is_ambiguous\n      ,cast(0 as bit) as is_invalid\ninto #dst\nfrom #dst_stage a\njoin #dst_stage b on a.ordinal + 1 = b.ordinal\nunion all\nselect a.dst_date + ' 02:00' as start_date\n      ,a.dst_date + ' 03:00' as end_date\n      ,cast(1 as bit) as is_dst\n      ,cast(0 as bit) as is_ambiguous\n      ,cast(1 as bit) as is_invalid\nfrom #dst_stage a\nwhere a.time_period = 'start of dst'\nunion all\nselect a.dst_date + ' 01:00' as start_date\n      ,a.dst_date + ' 02:00' as end_date\n      ,cast(0 as bit) as is_dst\n      ,cast(1 as bit) as is_ambiguous\n      ,cast(0 as bit) as is_invalid\nfrom #dst_stage a\nwhere a.time_period = 'end of dst'\norder by 1\n\n-------------------------------------------------------------------------------\n\n-- Test Eastern\nselect\n    the_date as eastern_local\n    ,todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end) as eastern_local_tz\n    ,switchoffset(todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end), '+00:00') as utc_tz\n    --,b.*\nfrom (\n    select cast('2015-03-08' as datetime) as the_date\n    union all select cast('2015-03-08 02:30' as datetime) as the_date\n    union all select cast('2015-03-08 13:00' as datetime) as the_date\n    union all select cast('2015-11-01 01:30' as datetime) as the_date\n    union all select cast('2015-11-01 03:00' as datetime) as the_date\n) a left join\n#dst b on b.start_date &lt;= a.the_date and a.the_date &lt; b.end_date\n</code></pre>\n"}, {'answer_id': 55822437, 'author': 'Shakespeare101', 'author_id': 11403003, 'author_profile': 'https://Stackoverflow.com/users/11403003', 'pm_score': 0, 'selected': False, 'text': "<pre><code>--Adapted Bob Albright and WillDeStijl suggestions for SQL server 2014\n--\n--In this instance I had no dates prior to 2006, therefore I simplified the case example\n--I had to add the variables for the assignment to allow trimming the timestamp from my resultset \n\nDECLARE @MARCH_DST as DATETIME\nSET @MARCH_DST='3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(getdate()) as varchar) + ' 7:00'\n\nDECLARE @NOV_DST as DATETIME\nSET @NOV_DST='11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(getdate()) as varchar) + ' 6:00'\n\nselect cast(dateadd(HOUR,\n-- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007. \n        -- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on \n        -- the first Sunday of November, one week later than in years past. This change resulted in a new DST period \n        -- that is four weeks (five in years when March has five Sundays) longer than in previous years. In 2008 \n        -- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8\n       CASE WHEN\n                date1 &gt;=@MARCH_DST\n            AND\n                date1&lt; @NOV_DST\n       THEN -4 ELSE -5 END\n       , date1) as DATE) as date1_edited\n\n</code></pre>\n"}, {'answer_id': 70235084, 'author': 'Suneel Kumar', 'author_id': 14871525, 'author_profile': 'https://Stackoverflow.com/users/14871525', 'pm_score': 0, 'selected': False, 'text': "<p>I found Simple Way to convert any date to any timezone.\nCurrently i have changed date to India Standard Time</p>\n<pre>\n\n    \nDECLARE @SqlServerTimeZone VARCHAR(50)\n\nDECLARE @LocalTimeZone VARCHAR(50)='India Standard Time'\n\nEXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',\n'SYSTEM\\CurrentControlSet\\Control\\TimeZoneInformation',\n'TimeZoneKeyName',@SqlServerTimeZone OUT\n\n\nDECLARE @DateToConvert datetime= GetDate()\n\nSELECT LocalDate =  @DateToConvert AT TIME ZONE @SqlServerTimeZone AT TIME ZONE  @LocalTimeZone\n\n\n\n\n</pre>\n"}]
['https://Stackoverflow.com/questions/24797', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1950/']