title: Best way to get past 7 days in sql server?
body: <p>I need the results of the past 24 hour, 7 day period.  My desired results need to be in this format:</p>
  
  <pre><code>2011-11-05   11 orders  $12354.94
  2011-11-04   12 orders  $12555.72
  2011-11-03   5 orders  $1000.24
  </code></pre>
  
  <p>While experimenting with some queries, I keep getting a different amount of rows returned. For example, this query:</p>
  
  <pre><code>SELECT CAST(Left(OrderDate,10) as smalldatetime) as PurchaseDate,
         COUNT(Orders.WebOrderNumber) as "Rows",
         SUM(cast(Orders.Total_Lines_Net AS money)) as Total
  FROM Orders 
  WHERE OrderDate  BETWEEN getdate() - 7 AND getdate() 
  GROUP BY cast(Left(OrderDate,10) as smalldatetime)
  ORDER BY cast(Left(OrderDate,10) as smalldatetime)
  </code></pre>
  
  <p>Returns this Data: </p>
  
  <pre><code>   PurchaseDate Rows    Total
  2011-12-01 00:00:00 1539    404798.85
  2011-12-02 00:00:00 1572    324963.604
  2011-12-03 00:00:00 970 119244.93
  2011-12-04 00:00:00 1092    120888.244
  2011-12-05 00:00:00 1201    337646.952
  2011-12-06 00:00:00 1309    287609.243
  2011-12-07 00:00:00 1555    315223.22
  </code></pre>
  
  <p>I thought that the rows for December 1st seemed low, so I tried a different query:</p>
  
  <pre><code>SELECT   SUM(cast(Orders.Total_Lines_Net AS money)) as Total,
           COUNT(Orders.WebOrderNumber) as "Rows",
           CAST(Left(OrderDate,10) as smalldatetime) as PurchaseDate
  FROM Orders 
  WHERE OrderDate between '2011-12-01 00:00:00' AND  + '2011-12-01 23:59:59'
  GROUP BY cast(Left(OrderDate,10) as smalldatetime)
  </code></pre>
  
  <p>Which returns 2597 rows.  Is the first query not returning the full 24 hour period? The date field in this database is a <code>varchar(255)</code>, which contains the date, hour, minute, and seconds.  Whats the right way to do this?</p>