title: Ordering dates for 4 weeks
body: <p>I am writing a SQL query that pulls some information for the last 4 weeks.
   I am seeing two issues with my results (see below). </p>
  
  <p><strong>First problem</strong> is that when I look back four weeks, the range should be August 10 - September 6. When I order by 'Day of the Month', the dates in September get moved to the top of the results when they should actually be at the end. So my results should start from 10 (august) and end at 6 (September). </p>
  
  <p><strong>Second problem</strong> is I'm missing a few random dates (3, 4, 13, 27).</p>
  
  <pre><code>Day of the Month    Number of X
  1                   125
  2                   77
  5                   5
  6                   23
  10                  145
  11                  177
  12                  116
  14                  2
  15                  199
  16                  154
  17                  134
  18                  140
  19                  154
  21                  8
  22                  166
  23                  145
  24                  151
  25                  107
  26                  79
  28                  3
  29                  151
  30                  163
  31                  147
  </code></pre>
  
  <p>Here a general version of my query:</p>
  
  <pre><code>DECLARE @startDate datetime, @endDate datetime;
  SET @startDate = dateadd(day, -28, GETDATE());
  SET @endDate = dateadd(day, -1, GETDATE());
  
  Select DATEPArt(dd, Time) AS 'Day of the Month', count(*) AS ' Number of X' 
  from SomeTable ST
  where Time &gt;= @startDate
  AND Time &lt;  @endDate 
  group by DATEPArt(dd, Time)   
  order by 'Day of the Month'  
  </code></pre>