Pass a column as parameter to dateadd in SQL Server
<p>I want to convert a column of UTC time to local time. </p>

<p>My data looks like this: </p>

<pre><code>time_utc                TZID            timezone
------------------------------------------------
2014-02-27 12:00:39.0   America/Toronto     -5
2013-05-21 09:35:30.0   America/Goose_Bay   -4
2015-01-08 06:58:58.0   America/Creston     -7
</code></pre>

<p>I know that using </p>

<pre><code>select *, DATEADD(hour, 5,time_utc)
from mytable
</code></pre>

<p>will add 5 hours to column <code>time_utc</code>. </p>

<p>However, as you can see, I have a variable time zone column. </p>

<p>How can I pass this variable to the <code>dateadd</code> function? </p>

<p>I tried the following 2 commands but they don't work: </p>

<p>Attempt #1:</p>

<pre><code>select *, DATEADD(hour, timezone, time_utc)
from mytable
</code></pre>

<p>Attempt #2:</p>

<pre><code>select *, DATEADD(hour, (select timezone from mytable), time_utc)
from mytable
</code></pre>

<p>Both throws this error: </p>

<blockquote>
  <p>Argument data type varchar is invalid for argument 2 of dateadd function. [SQL State=S0001, DB Errorcode=8116] </p>
</blockquote>

<p>For decimal values of timezone, for instance -3.5, how would this work? </p>

<p>Thanks</p>