title: MySQL Date Between
body: <p><strong>FIXED - this is what I have now</strong> - Written in ASP</p>
  
  <pre><code>If Session("dateRange") = "Today" Then
          fromDate = Date()
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated = '"&amp;fromDate&amp;"' "
  ElseIf Session("dateRange") = "Yesterday" Then
          fromDate = DateAdd("d",-1,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated = '"&amp;fromDate&amp;"' "
  ElseIf Session("dateRange") = "1 Week" Then
          fromDate = DateAdd("d",-7,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' "
  ElseIf Session("dateRange") = "1 Month" Then
          fromDate = DateAdd("m",-1,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' "
  ElseIf Session("dateRange") = "3 Months" Then
          fromDate = DateAdd("m",-3,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' "
  ElseIf Session("dateRange") = "6 Months" Then
          fromDate = DateAdd("m",-6,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' "
  ElseIf Session("dateRange") = "1 Year" Then
          fromDate = DateAdd("yyyy",-1,Date())
          fromDate = Year(fromDate)&amp;"-"&amp;Month(fromDate)&amp;"-"&amp;Day(fromDate)
          whereClause = whereClause &amp; "AND dateCreated BETWEEN '"&amp;fromDate&amp;"' AND '"&amp;toDate&amp;"' " 
  End If
  </code></pre>
  
  <hr>
  
  <p><strong>Original Question:</strong></p>
  
  <p>Simple question but for some reason I cannot seem to get this working.</p>
  
  <p>I have a search box, next to that is a date-range select menu, so the user can search for photos within the last 1 month, 6 months or 12 months.</p>
  
  <p>What I have does not error but it produces no results when it should do, this is it:</p>
  
  <pre><code>WHERE dateCreated BETWEEN "&amp;DateAdd("m",-6,Date())&amp;" AND "&amp;Date()&amp;"
  </code></pre>
  
  <p>The SQL output produces this:</p>
  
  <pre><code>dateCreated BETWEEN 18/03/2011 AND 18/09/2011 ORDER BY dateCreated DESC
  </code></pre>
  
  <p>The database 'dateCreated' field is set to (Date INDEX).</p>
  
  <p>Can anybody see what is wrong?</p>