MySQL之间的日期

Question

已修正-这是我现在所拥有的 -用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

原始问题：

简单的问题，但由于某种原因，我似乎无法正常工作。

我有一个搜索框，旁边是日期范围选择菜单，因此用户可以搜索最近1个月，6个月或12个月内的照片。

我所拥有的没有错误，但是当它应该执行时它不会产生任何结果，就是这样：

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

SQL输出产生以下结果：

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

数据库的’dateCreated’字段设置为（Date INDEX）。

谁能看到哪里出了问题？

Answer 1

结果SQL中的日期应用单引号引起来。尝试用引号将查询的动态部分引起来。像这样：

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

我假设会产生此SQL：

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

另外，看看是否可以找到一种以更常用的MySQL yyyy-mm-dd格式格式化日期的方法。像这样：

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