FIXED - this is exactly what I've now - Designed 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 whatever reason I am unable to appear to obtain this working.

I've got a search box, alongside that's to start dating ?-range choose menu, therefore the user can look for photos in the last 30 days, 6 several weeks or 12 several weeks.

Things I have doesn't error however it produces no results if this must do, here it is:

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' area is placed to (Date INDEX).

Can anybody see what's wrong?

The dates inside your resulting SQL ought to be cited with single quotes. Try all around the dynamic areas of your query with quotes. Something similar to this:

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

that we assume would produce this SQL:

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

Also, try to try to format the dates within the more usual MySQL yyyy-mm-dd format. Such as this:

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

%d-%m-%Y isn't a proper date format and date values in MySQL ought to be always cited alike strings and ISO 8601 ('%Y-%m-%d') format. 18/03/2011 is going to be construed as just 18 divided by 03 and 2011 which provides about 0.002….

In other words, your SQL ought to be such as the following form rather:

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

If you're particularly searching backwards from "now", why don't you 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 requirement for a "Range", just place the month range back you need to allow...

Unsure what technology you are using for DateAdd(), however your final date format is wrong additionally that your date values aren't enclosed by single quotes.

MySQL compares dates as 2011-03-18 not 18/03/2011.

An email that can be done this in MySQL using its Date Time Functions. Even though it may keep your query from being cached.