Search This Blog

Monday, November 12, 2018

BETWEEN operator in MySQL

 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). ( It is inclusive)
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
        -> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0
For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

One thing need to pay attention to is that for example:
BETWEEN '2014-01-01' AND '2014-07-01'
And your column is datetime, then it actually includes '2014-07-01 00:00:00' but not the other values of that date such as '2014-07-01 08:08:08'.

So date(mYdatetime) BETWEEN '2014-01-01' AND '2014-07-01' will include the whole day of '2014-07-01', but mYdatetime BETWEEN '2014-01-01' AND '2014-07-01' does NOT include the day of '2014-07-01' except the time of '2014-07-01 00:00:00'
 expr NOT BETWEEN min AND max
This is the same as NOT (expr BETWEEN min AND max).