0

I have come across the following queries, but not sure if there is any difference between them,

select * from table where date between fromdate and todate

V/S

select * from table where date >= fromdate and date <= todate

if we are using the first query, does it include the date on the from & to date? i.e. between 16/10/2012 and 22/10/2012, does it include 16th and 22th ?

Thank you so much.

jsist
  • 5,223
  • 3
  • 28
  • 43
Will
  • 13
  • 6

6 Answers6

1

Yes, it includes them.
That's why I avoid BETWEEN. The name is at least ambiguous. Different people understand different things and your question also shows that.

For example, in my understanding, between is exclusive but in SQL it is inclusive.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
  • It seems like in mySQL it sets date as YYYY-MM-DD 00:00:00, when there is no time defined then, it will be default to 00:00:00. I have a date column which has data like 2012-09-10 18:10:40. When I use the following code, it doesnt include this data select * from table where date between 2012-08-10 and 2012-09-10. It only include the data before 10th Sept. IS there a way to change that the SQL will use 2012-09-10 23:59:59. – Will Oct 04 '12 at 13:01
  • @Will: That has nothing to do with the `between` keyword or your question in general. Please post a new question for this problem. – Daniel Hilgarth Oct 04 '12 at 13:02
1

There is no difference.

(a BETWEEN b AND c)

is the same as writing

((a >= b) AND (a <= c))
John Woo
  • 258,903
  • 69
  • 498
  • 492
0

No difference

both query will give the same result

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
0

There is no difference:

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

Miroslav
  • 1,960
  • 1
  • 13
  • 26
0

Yes they are included. The first one is just a shortcut for the second one, so they are equivalent.

Nelson
  • 49,283
  • 8
  • 68
  • 81
0

You can check some explanation on between here: MySQL "between" clause not inclusive? It also reads that using <= & => is faster

Community
  • 1
  • 1
Marco Gabriel Godoy Lema
  • 628
  • 8
  • 19