title: Change only time part of a datetimeoffset in SQL
body: <p>How can I set only the time portion of a SQL datetimeoffset variable?</p>
  
  <p>For example:</p>
  
  <pre><code>DECLARE @date datetimeoffset = '2014-01-11 11:04:26 +02:00'
  </code></pre>
  
  <p>I want to calculate two further datetimeoffsets:</p>
  
  <pre><code>@startdate should be '2014-01-11 00:00:00 +02:00'
  @enddate should be '2014-01-12 00:00:00 +02:00'
  </code></pre>
  
  <p>But all the solutions I tried set the offset to zero.</p>
  
  <p><strong>NOTE</strong>: I need the original offset in the results since I need to compare them to some datetimeoffset columns in a table. So just casting to a date will not work.</p>
  
  <p><strong>Further note</strong>: This would be quite easy in SQL2012; I could use DATETIMEOFFSETFROMPARTS. Unfortunately, I am not able to upgrade to 2012 at the moment. Example:</p>
  
  <pre><code>SET @startdate = SELECT DATETIMEOFFSETFROMPARTS(datepart(yyyy, @date), datepart(mm, @date), datepart(dd, @date), 0, 0, 0, 0, datepart(tz, @date), 0, 7);
  </code></pre>