SQL 查询以选择两个日期之间的数据，格式为 m/d/yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14681815/
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
Query to select data between two dates with the format m/d/yyyy
提问by Rahul Gautam
I am facing problem when i'm trying to select records from a table between two dates.
当我尝试从两个日期之间的表中选择记录时，我遇到了问题。
m using the following query
m 使用以下查询
select * from xxx where dates between '10/10/2012' and '10/12/2012'
this query works for me but when the dates are in format like 1/1/2013.. it doesn't work..
此查询对我有用，但是当日期格式为 1/1/2013 时.. 它不起作用..
plz solve my problem ASAP.
请尽快解决我的问题。
回答by Aleksandr Fedorenko
This solution provides CONVERT_IMPLICIT operation for your condition in predicate
此解决方案为谓词中的条件提供 CONVERT_IMPLICIT 操作
SELECT *
FROM xxx
WHERE CAST(dates AS date) BETWEEN '1/1/2013' and '1/2/2013'
OR
或者
SELECT *
FROM xxx
WHERE CONVERT(date, dates, 101) BETWEEN '1/1/2013' and '1/2/2013'
Demo on SQLFiddle
回答by user2001117
Try this
尝试这个
SELECT *
FROM xxx
WHERE dates BETWEEN STR_TO_DATE('10/10/2012', '%m/%d/%Y')
AND STR_TO_DATE('10/12/2012', '%m/%d/%Y') ;
or
或者
SELECT *
FROM xxx
WHERE STR_TO_DATE(dates , '%m/%d/%Y') BETWEEN STR_TO_DATE('10/10/2012', '%m/%d/%Y')
AND STR_TO_DATE('10/12/2012', '%m/%d/%Y') ;
回答by ShadowUC
$Date3 = date('y-m-d');
$Date2 = date('y-m-d', strtotime("-7 days"));
SELECT * FROM disaster WHERE date BETWEEN '".$Date2."' AND '".$Date3."'
回答by Bipul Roy
you have to split the datetime and then store it with your desired format like dd/MM/yyyy. then you can use this query with between but i have objection using this becasue it will search every single data on your database,so i suggest you can use datediff.
您必须拆分日期时间，然后将其存储为您想要的格式，如 dd/MM/yyyy。那么你可以使用这个查询，但我反对使用它，因为它会搜索你数据库中的每一个数据，所以我建议你可以使用 datediff。
Dim start = txtstartdate.Text.Trim()
Dim endday = txtenddate.Text.Trim()
Dim arr()
arr = Split(start, "/")
Dim dt As New DateTime
dt = New Date(Val(arr(2).ToString), Val(arr(1).ToString), Val(arr(0).ToString))
Dim arry()
arry = Split(endday, "/")
Dim dt2 As New DateTime
dt2 = New Date(Val(arry(2).ToString), Val(arry(1).ToString), Val(arry(0).ToString))
qry = "SELECT * FROM [calender] WHERE datediff(day,'" & dt & "',[date])>=0 and datediff(day,'" & dt2 & "',[date])<=0 "
here i have used dd/MM/yyyy format.
这里我使用了 dd/MM/yyyy 格式。
回答by Patrick Honorez
select * from xxx where dates between '2012-10-10' and '2012-10-12'
I always use YYYY-MM-DDin my views and never had any issue. Plus, it is readable and non equivocal.
You should be aware that using BETWEENmight not return what you expect with a DATETIMEfield, since it would eliminate records dated '2012-10-12 08:00'for example.
I would rather use where dates >= '2012-10-10' and dates < '2012-10-13'(lower than next day)
我总是YYYY-MM-DD在我的观点中使用，从来没有任何问题。此外，它具有可读性和明确性。
您应该知道 usingBETWEEN可能不会返回您对DATETIME字段的期望，因为它会消除'2012-10-12 08:00'例如过时的记录。
我宁愿使用where dates >= '2012-10-10' and dates < '2012-10-13'（低于第二天）
回答by S M Jobayer Alam
SELECT * FROM tablename WHERE STR_TO_DATE(columnname, '%d/%m/%Y')
BETWEEN STR_TO_DATE('29/05/2017', '%d/%m/%Y')
AND STR_TO_DATE('30/05/2017', '%d/%m/%Y')
It works perfectly :)
它完美地工作:)
回答by user2001117
By default Mysql store and return ‘date' data type values in “YYYY/MM/DD” format. So if we want to display date in different format then we have to format date values as per our requirement in scripting language
默认情况下，Mysql 以“YYYY/MM/DD”格式存储和返回“日期”数据类型值。因此，如果我们想以不同的格式显示日期，那么我们必须根据脚本语言的要求格式化日期值
And by the way what is the column data type and in which format you are storing the value.
顺便说一下，列数据类型是什么以及以哪种格式存储值。
回答by namo_rocks
use this
用这个
select * from xxx where dates between '10/oct/2012' and '10/dec/2012'
you are entering string, So give the name of month as according to format...
您正在输入字符串，因此请根据格式输入月份名称...
回答by Ankyy
Try this:
尝试这个：
select * from xxx where dates between convert(datetime,'10/10/2012',103) and convert(dattime,'10/12/2012',103)
回答by user3600122
DateTime dt1 = this.dateTimePicker1.Value.Date;
DateTime dt2 = this.dateTimePicker2.Value.Date.AddMinutes(1440);
String query = "SELECT * FROM student WHERE sdate BETWEEN '" + dt1 + "' AND '" + dt2 + "'";
