what is between in mysql?

MySQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

The MySQL BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).

When using the MySQL BETWEEN Condition with dates, be sure to use the CAST function to explicitly convert the values to dates.

Between Operator returns true if the data is within a range.

The Between Operator will return records including the starting and ending values.

Between operator support only the AND operator in MySQL.

With Numeric

SELECT * FROM contacts WHERE contact_id BETWEEN 100 AND 200;

This MySQL BETWEEN example would return all rows from the contacts table where the contact_id is between 100 and 200 (inclusive). It is equivalent to the following SELECT statement:

SELECT * FROM contacts WHERE contact_id >= 100 AND contact_id <= 200;

With Date

When using the BETWEEN condition in MySQL with dates, be sure to use the CAST function to explicitly convert the values to dates.

SELECT * FROM order_details WHERE order_date BETWEEN CAST('2014-02-01' AS DATE) AND CAST('2014-02-28' AS DATE);

It would be equivalent to the following SELECT statement:

SELECT * FROM order_details WHERE order_date >= CAST('2014-02-01' AS DATE) AND order_date <= CAST('2014-02-28' AS DATE);

NOT Operator

SELECT * FROM suppliers WHERE supplier_id NOT BETWEEN 2000 AND 2999; SELECT name,country,city,dob FROM users WHERE YEAR(dob) NOT BETWEEN 1984 AND 2000;

SELECT * FROM suppliers WHERE supplier_id < 2000 OR supplier_id > 2999;

SELECT name,country,city,date_of_birth FROM users
WHERE MONTH(date_of_birth) BETWEEN '03' and '08';
1969-04-25
1985-10-01
1975-07-05
1984-09-02
1975-01-01
output :-
1969-04-25
1975-07-05

SELECT name,country,city,date_of_birth FROM users WHERE MONTH(date_of_birth) BETWEEN '05' AND '09' AND YEAR(date_of_birth) BETWEEN 1950 AND 1975;

Not Between

The NOT BETWEEN Operator is just the opposite of BETWEEN Operator. NOT BETWEEN operator will return data where the column values not in between the range values.

SELECT * FROM customers WHERE Age NOT BETWEEN 18 AND 21;

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)