title: Dealing with Timezones and Daylight savings in Sql Server 2005 and up
body: <p>Soo... I have a web app i'm working on.  Here's the basics. (ASP.Net, C#, EF, Linq)</p>
  
  <p>We have Projects which have and open and close date.</p>
  
  <p>So imagine a table called projects with those fields.
  On that table we want a computed column for Time Open and Adjusted time open where Adjusted Time Open takes into account hours of operation.</p>
  
  <p>right now you can set hours of operation from Sunday - Saturday or select that the day is not worked.</p>
  
  <p>This is stored in a table and the values are stored as minutes from midnight, in UTC.</p>
  
  <p>Everything in our db as far as dates is stored in utc.</p>
  
  <p>So in the user defined function that calculates time open and adjusted time open (which are written and work, except for situations with daylight savings time.</p>
  
  <p>For example:</p>
  
  <p>We create a project where we're ahead one hour at 9am our local time. so that get's stored as 22:00:00 UTC.  If we create a project when we're back one our at 9am our local time, that get's stored as 21:00:00 UTC.  (I'm kinda guessing at the exact times, but you get the point).</p>
  
  <p>This is fine, I have no problems with this.  The problem comes in with hours of operation.</p>
  
  <p>We basically when they set it, get the utc time and then convert that to minutes from midnight and store that in the table.  So technically, that could be 22 * 60 or 21 * 60 depending on which part of daylight savings time we're in.  So the calculations could be off.</p>
  
  <p>Is this making sense?  I have a hard time grasping and explaining some of this stuff.</p>