title: SQL DateTimes stored in UTC get beginning of day based on offset
body: <p>This may be the most pathetic question ever asked related to SQL and date/time values, but I could use some help...</p>
  <p>Trying to setup a function/job that will run at a specified time or times in eastern, mountain, central, and pacific time zones (in theory other zones would work too).  The system will identify which users belong to each timezone and then output data from the system highlighting what they've accomplished for the current day.</p>
  <p>Here is my challenge, I know all date/time values are stored on the SQL DB in UTC.  I can apply the offset and convert those times to local time zones.  Rather than convert tens of thousands of date/time values to local time and make comparisons there, it'd be cleaner (I think) to simply adjust the beginning and ending date/time values of UTC within the stored procedure.</p>
  <p>On the west coast it is currently just about <code>2017-09-30 14:30:00</code> and in UTC is <code>2017-09-30 21:30:00</code>, this clearly demonstrates a 7 hour time zone difference right now which means &quot;today&quot; from a user perspective technically started at <code>2017-09-30 07:00:00</code> and will end on <code>2017-10-01 06:59:999</code> in UTC.</p>
  <p>What is the best way of establishing these date/time values for a users beginning of day and ending of day values?</p>
  <h3>UPDATES</h3>
  <p>I currently have this code...</p>
  <pre><code>DECLARE @InputDate as DateTime
  DECLARE @InputEndDate as DateTime
  DECLARE @InputDateWithOffset as DateTimeOffSet
  DECLARE @InputEndDateWithOffset as DateTimeOffSet
  
  SET @InputDate = '2017-09-28'
  SET @InputDateWithOffset = @InputDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
  
  SET @InputEndDate = DATEADD(day, 1, DATEADD(ms, -3, @InputDate))
  SET @InputEndDateWithOffset = @InputEndDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
  
  SELECT 
      @InputDate AS InputDate, @InputEndDate AS InputEndDate, 
      @InputDateWithOffset AS InputDateWithOffset, 
      @InputEndDateWithOffset AS InputEndDateWithOffset
  </code></pre>
  <p>Which outputs the following:
  <a href="https://i.stack.imgur.com/YkRcj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YkRcj.png" alt="enter image description here" /></a></p>
  <p>The last two columns appear to be correct as it would represent both the beginning of the Input Date and the ending of the Input Date as the Input Date is going to be the local date of the execution...</p>
  <p>When I take the <code>@InputDateWithOffset</code> and <code>@InputEndDateWithOffset</code> against my table values with datetimes in UTC, it appears the only dates being returned are those that fall on <code>2017-09-28</code> and seems to disregard the comparisons to the Offset date/times.</p>