title: SQL Server 2008 time zone conversion
body: <p>In my web app, all date values are converted to UTC before being passed into the SQL database and then converted back to local time whenever they are displayed in the app. So the bottom line is that all dates are in UTC in the database.</p>
  
  <p>That worked just fine until I started report development. Crystal Reports has no ability (that I can detect) to do a time zone conversion so I need to do the conversion within SQL before passing the value to the report.</p>
  
  <p>SQL Server appears to have a much more limited ability to convert datetime values. The only function I see is SWITCHOFFSET. The problem with this function is that it is not daylight-savings aware which seems to be a major flaw. In asp.net, I can pass a datetime value with a time zone and it will automatically convert it to UTC time, taking account of any needed daylight savings time adjustments.</p>
  
  <p>In SQL though, instead of saying something like </p>
  
  <pre><code>SWITCHOFFSET (SomeDate,"Eastern Time"),
  </code></pre>
  
  <p>I now have to say </p>
  
  <pre><code>SWITCHOFFSET(SomeDate, "-4:00"). 
  </code></pre>
  
  <p>But what happens if I'm pulling data from a table and I ask for all of the data for March, the month when daylight savings starts? No matter what, some of it will be incorrect. I can either say -4:00 or -5:00 but obviously, not both.</p>
  
  <p>Is there some other function to adjust for this as, frankly, it seems SWITCHOFFSET is half baked if it doesn't know how to do a daylight savings conversion.</p>