MySQL之间的日期 [英] MySQL Date Between

问题描述

FIXED - 这是我现在的 - 写在ASP

  dateRange）=Todaythen 
 fromDate = Date（）
 fromDate = Year（fromDate）& - & Month（fromDate）& - & b $ b whereClause = whereClause& AND dateCreated ='& fromDate&'
 ElseIf Session（dateRange）=昨天然后
 fromDate = DateAdd（d， -  1，Date $ b fromDate = Year（fromDate）& - & Month（fromDate）& - & Day（fromDate）
 whereClause = whereClause& AND dateCreated ='& fromDate&'
 ElseIf Session（dateRange）=1 Weekthen 
 fromDate = DateAdd（d， -  7，Date b $ b fromDate = Year（fromDate）& - & Month（fromDate）& - & Day（fromDate）
 whereClause = whereClause& AND dateCreated BETWEEN'& fromDate&'AND'& toDate&'
 ElseIf Session（dateRange）=1 Monththen 
 fromDate = DateAdd（m ，-1，Date（））
 fromDate = Year（fromDate）& - & Month（fromDate）& - & Day（fromDate）
 whereClause = whereClause& AND dateCreated BETWEEN'& fromDate&'AND'& toDate&'
 ElseIf Session（dateRange）=3 Monthsthen 
 fromDate = DateAdd（m ，-3，Date（））
 fromDate = Year（fromDate）& - & Month（fromDate）& - & Day（fromDate）
 whereClause = whereClause& AND dateCreated BETWEEN'& fromDate&'AND'& toDate&'
 ElseIf Session（dateRange）=6 Monthsthen 
 fromDate = DateAdd（m ，-6，Date（））
 fromDate = Year（fromDate）& - & Month（fromDate）& - & Day（fromDate）
 whereClause = whereClause& AND dateCreated BETWEEN'& fromDate&'AND'& toDate&'
 ElseIf Session（dateRange）=1 Yearthen 
 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字段设置为（日期IND​​EX）。

任何人都能看到什么错误？

解决方案

生成的SQL中的日期应该用单引号引起来。尝试使用引号包围查询的动态部分。像这样：

  WHERE dateCreated BETWEEN'& DateAdd（m， -  6，Date（））& 'AND'& Date（）&'
  

：

  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 
  
 
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?
解决方案
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


                        
这篇关于MySQL之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！