TheCarver TheCarver - 1 month ago 6
MySQL Question

MySQL Date Between

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

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
Comments