MySQL Date Between

Question

FIXED - this is what I have now - Written in ASP

If Session("dateRange") = "Today" Then
        fromDate = Date()
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated = '"&fromDate&"' "
ElseIf Session("dateRange") = "Yesterday" Then
        fromDate = DateAdd("d",-1,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated = '"&fromDate&"' "
ElseIf Session("dateRange") = "1 Week" Then
        fromDate = DateAdd("d",-7,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated BETWEEN '"&fromDate&"' AND '"&toDate&"' "
ElseIf Session("dateRange") = "1 Month" Then
        fromDate = DateAdd("m",-1,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated BETWEEN '"&fromDate&"' AND '"&toDate&"' "
ElseIf Session("dateRange") = "3 Months" Then
        fromDate = DateAdd("m",-3,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated BETWEEN '"&fromDate&"' AND '"&toDate&"' "
ElseIf Session("dateRange") = "6 Months" Then
        fromDate = DateAdd("m",-6,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated BETWEEN '"&fromDate&"' AND '"&toDate&"' "
ElseIf Session("dateRange") = "1 Year" Then
        fromDate = DateAdd("yyyy",-1,Date())
        fromDate = Year(fromDate)&"-"&Month(fromDate)&"-"&Day(fromDate)
        whereClause = whereClause & "AND dateCreated BETWEEN '"&fromDate&"' AND '"&toDate&"' " 
End If

---

Original Question:

Simple question but for some reason I cannot seem to get this working.

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.

What I have does not error but it produces no results when it should do, this is it:

WHERE dateCreated BETWEEN "&DateAdd("m",-6,Date())&" AND "&Date()&"

The SQL output produces this:

dateCreated BETWEEN 18/03/2011 AND 18/09/2011 ORDER BY dateCreated DESC

The database 'dateCreated' field is set to (Date INDEX).

Can anybody see what is wrong?

Answer 1

The dates in your resulting SQL should be quoted with single quotes. Try surrounding the dynamic parts of your query with quotes. Something like this:

WHERE dateCreated BETWEEN '"&DateAdd("m",-6,Date())&"' AND '"&Date()&"'

which I assume would produce this SQL:

dateCreated BETWEEN '18/03/2011' AND '18/09/2011' ORDER BY dateCreated DESC

Also, see if you can find a way to format the dates in the more usual MySQL yyyy-mm-dd format. Like this:

dateCreated BETWEEN '2011-03-18' AND '2011-09-18' ORDER BY dateCreated DESC

Answer 2

If you are specifically looking backwards from "now", why not do

where DateCreated >= DATE_SUB( CURDATE(), INTERVAL 1 MONTH )
where DateCreated >= DATE_SUB( CURDATE(), INTERVAL 6 MONTH )
where DateCreated >= DATE_SUB( CURDATE(), INTERVAL 12 MONTH )

No need for a "Range", just insert the month range back you want to allow...