MySQL 日期之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7464271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Date Between
提问by TheCarver
FIXED - this is what I have now- Written in ASP
已修复 - 这就是我现在所拥有的- 用 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.
我有一个搜索框，旁边是一个日期范围选择菜单，因此用户可以搜索过去 1 个月、6 个月或 12 个月内的照片。
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:
SQL 输出产生这个：
dateCreated BETWEEN 18/03/2011 AND 18/09/2011 ORDER BY dateCreated DESC
The database 'dateCreated' field is set to (Date INDEX).
数据库“dateCreated”字段设置为（日期索引）。
Can anybody see what is wrong?
任何人都可以看到有什么问题吗？
回答by Asaph
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:
生成的 SQL 中的日期应该用单引号引用。尝试用引号包围查询的动态部分。像这样的东西：
WHERE dateCreated BETWEEN '"&DateAdd("m",-6,Date())&"' AND '"&Date()&"'
which I assume would produce this SQL:
我假设会产生这个 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-ddformat. Like this:
另外，看看您是否可以找到一种以更常见的 MySQLyyyy-mm-dd格式格式化日期的方法。像这样：
dateCreated BETWEEN '2011-03-18' AND '2011-09-18' ORDER BY dateCreated DESC
回答by DRapp
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...
不需要“范围”，只需插入您想要允许的月份范围......
回答by minhee
%d-%m-%Yis not a proper date format and date values in MySQL should be always quoted alike strings and ISO 8601 ('%Y-%m-%d') format. 18/03/2011will be interpreted as just 18divided by 03and 2011which gives about 0.002….
%d-%m-%Y不是正确的日期格式，MySQL 中的日期值应始终以类似字符串和 ISO 8601 ( '%Y-%m-%d') 格式引用。18/03/2011将被解释为刚刚18除以03和2011给出约0.002…。
That is to say, your SQL should be like the following form instead:
也就是说，你的 SQL 应该像下面这样：
dateCreated BETWEEN '2011-03-18' AND '2011-09-18' ORDER BY dateCreated DESC
回答by Jason McCreary
Not sure what technology you're using for DateAdd(), but your final date format is wrong in addition to the fact that your date values are not enclosed by single quotes.
不确定您使用的是什么技术DateAdd()，但除了您的日期值没有用单引号括起来之外，您的最终日期格式是错误的。
MySQL compares dates as 2011-03-18not 18/03/2011.
MySQL 比较日期为2011-03-18not 18/03/2011。
A note that you can do this in MySQL with its Date Time Functions. Although it maykeep the query from being cached.
请注意，您可以在 MySQL 中使用它的Date Time Functions执行此操作。虽然它可能会阻止查询被缓存。
回答by dlawrence
If those strings are unquoted, I believe you are actually just doing division and then comparing the dates between a very small timeframe.
如果这些字符串没有被引用，我相信你实际上只是在做除法，然后在一个非常小的时间范围内比较日期。
