title: Sorting/Grouping SQL by day-of-week and hour-of-day
body: <p>I have a SQL Server 2005 table that contains records with a UTC/GMT date field.
  I need to have the records GROUPed BY and ORDERed by the "day of the week" and "hour of the day".  Giving 7*24=168 groups.  The output would look like:</p>
  
  <pre><code>Sun 12am
  Sun  1am
  Sun  2am
  .... etc
  Mon 12am
  Mon  1am
  .... etc
  .... etc
  Sat 10pm
  Sat 11pm
  </code></pre>
  
  <p>Everything works fine until I try to convert things to my local time zone. 
  Doing the conversion inside SQL:</p>
  
  <pre><code>SELECT MIN(Key),MIN(SavedOn), 
  FROM MyTable 
  GROUP BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) - (5.0/24.0)
  ORDER BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) - (5.0/24.0)
  </code></pre>
  
  <p>Or doing the conversion in my own code AFTER sorting/getting the records in UTC:</p>
  
  <pre><code>SELECT MIN(Key),MIN(SavedOn), 
  FROM MyTable 
  GROUP BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn)  
  ORDER BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) 
  
  (my own math here)
  </code></pre>
  
  <p>Either way... 5 records will appear "out of order".   The end of the week records (Sat PM)
  appear at the beginning of the week... not the end... where they belong.</p>
  
  <p>Any idea on what I'm doing wrong?</p>