SQL中的BETWEEN子句 [英] BETWEEN clause in SQL

问题描述

我有一条SQL语句来显示两个日期之间的数据.我几乎明白了，但是有一个问题.

I have a SQL statement to display data between two dates. I almost got it but there's a problem.

如果我输入March 1,2012 to March 7, 2012 ..它应该显示两个日期之间的日期..但是它也显示2012年3月以下的所有日期.但是每当我输入2012年3月10日到2012年3月30日时，SQL完美的作品..任何帮助将不胜感激.谢谢

If I input March 1,2012 to March 7, 2012.. it should show data with dates between the two.. but it also show all of the dates under March 2012.. but whenever I input March 10, 2012 to March 30, 2012 the SQL works perfectly.. any help will be appreciated. thanks

SELECT 
   agentname, noofcalls, qualified, booking, resched, 
   actualbooking, sales, remarks, 
   concat(month,' ',day,',',year) as 'date' 
FROM 
   tblagents
WHERE 
   (month between '" & cbosmonth.Text & "' AND '" & cboemonth.Text & "')
   AND (day between '" & cbosday.Text & "' AND '" & cboeday.Text & "')
   AND (year between '" & cbosyear.Text & "' AND '" & cboeyear.Text & "')"

推荐答案

您正在对每个之间"进行字符串比较.每个以1、2或3开头的数字，无论后面是什么，即21或26或31，如果将它们视为字符串，它们都小于7. 1到30可以工作，因为您只剩下31，而30< 31也可以作为字符串.

you are doing string comparisons in each of your 'between'. Every number starting with a 1, a 2 or a 3, regardless of what follows it, i.e. 21, or 26, or 31, they are all lower than 7 if you look at them as strings. 1 to 30 works because you're only leaving 31 behind, and 30 < 31 as a String as well.

先进行串联，然后进行之间的串联:

Do the concatenation first and then the between:

WHERE concat(month,' ',day,',',year) 
      BETWEEN concat(cbosmonth.Text,' ', cbosday.Text,' ',cbosyear.Text)
      AND concat(cboemonth.Text,' ', cboeday.Text,' ',cboeyear.Text)

(检查语法是否正确，我只是从您的问题中复制粘贴内容，没有尝试过)

(check out for correct syntax, I'm just copy pasting from your question, not tried it)

顺便说一句，除非有理由，否则您可能应该将整个日期存储在具有正确数据时间(datetime，timestamp等)的单个列中，而不是三个独立的列中.

BTW, unless you have a reason to, you probably should be storing the entire date in a single column with the right data time (datetime, timestamp, ...) and not three separated columns.

这篇关于SQL中的BETWEEN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！