The SQL BETWEEN Operator selects values within a range. The values can be numbers, text, or dates. Is possible to select values outside a range using NOT BETWEEN. The BETWEEN operator can produce different result in different databases: In some databases excludes the test values, in some others include them and in some cases includes the first test value and excludes the last one.
Questions tagged [between]
1363 questions
22
votes
4 answers
Is there a performance difference between BETWEEN and IN with MySQL or in SQL in general?
I have a set of consecutive rows I want to get based upon their primary key, which is an auto-incrementing integer. Assuming that there are no holes, is there any performance between between:
SELECT * FROM `theTable` WHERE `id` IN (n, ... nk);…
pr1001
- 21,727
- 17
- 79
- 125
22
votes
7 answers
SQL Query NOT Between Two Dates
I need some help with SQL Query.
I am trying to select all records from table test_table which would not fit between two dates '2009-12-15' and '2010-01-02'.
This is my table structure:
`start_date` date NOT NULL default '0000-00-00',
`end_date`…
Kelvin
- 8,813
- 11
- 38
- 36
21
votes
6 answers
Datetime BETWEEN statement not working in SQL Server
I have the following query,
SELECT * FROM LOGS
WHERE CHECK_IN BETWEEN CONVERT(datetime,'2013-10-17') AND CONVERT(datetime,'2013-10-18')
this query not returning any result, but the following query return the result,
SELECT * FROM LOGS WHERE…
Able Alias
- 3,824
- 11
- 58
- 87
20
votes
2 answers
How to generate all dates between two dates
How can I retrieve all dates between '2015-10-02' to '2015-11-02' in SQLite? (String type)
Result will be like:
'2015-10-03'
'2015-10-04'
'2015-10-05'
...
'2015-11-01'
This is not a question about SELECT * FROM myTable where myDate <= '2015-01-01'…
Andiana
- 1,912
- 5
- 37
- 73
19
votes
5 answers
Why is Rails is adding `OR 1=0` to queries using the where clause hash syntax with a range?
The project that I'm working on is using MySQL on RDS (mysql2 gem specifically).
When I use a hash of conditions including a range in a where statement I'm getting a bit of an odd addition to my query.
User.where(id: [1..5])
and
User.where(id:…
Aaron
- 13,349
- 11
- 66
- 105
19
votes
4 answers
Mysql: Selecting values between two columns
I'm trying to select a value between 2 columns. Here is my dataset
id from to price
1 0.00 2.00 2.50
2 2.00 3.00 3.00
3 3.00 4.00 4.50
My goal, if I have a value of 2 is to select the line with the ID 1 (between…
user390749
18
votes
3 answers
Three.js - How can I calculate the distance between two 3D positions?
I've already tried searching several different things on Google. Doesn't seem like I'm able to find anything. Thought I might as well upload a question to Stack Overflow.
Thanks!
user3490600
- 255
- 1
- 3
- 8
16
votes
1 answer
How Do I Search Between a Date Range, Using the ActiveRecord Model?
I am new to both Ruby and ActiveRecord. I currently have a need to modify and existing piece of code to add a date range in the select. The current piece goes like this:
ReportsThirdparty.find(:all, :conditions => {:site_id=>site_id,…
Russ Bradberry
- 10,705
- 17
- 69
- 85
16
votes
5 answers
BETWEEN operator vs. >= AND <=: Is there a performance difference?
These two statements are logically equivalent:
SELECT * FROM table WHERE someColumn BETWEEN 1 AND 100
SELECT * FROM table WHERE someColumn >= 1 AND someColumn <= 100
Is there a potential performance benefit to one versus the other?
Jeff Meatball Yang
- 37,839
- 27
- 91
- 125
16
votes
11 answers
Why use the BETWEEN operator when we can do without it?
As seen below the two queries, we find that they both work well. Then I am confused why should we ever use BETWEEN because I have found that BETWEEN behaves differently in different databases as found in w3school
SELECT *
FROM employees
WHERE salary…
Thunder
- 10,366
- 25
- 84
- 114
16
votes
2 answers
mysql datetime field with index get a range 'like' vs. 'between and' performance
I just find mysql can query datetime using like:
like '2013-06-12%'
I think it can not use the index. I Google it, but can not find such subject directly. So I have a test using a table with 3308614 records. The first SQL:
SELECT * FROM…
bluearrow
- 856
- 2
- 11
- 26
16
votes
3 answers
SQL BETWEEN for text vs numeric values
BETWEEN is used in a WHERE clause to select a range of data between two values.
If I am correct whether the range's endpoint are excluded or not is DBMS specific.
What I can not understand in the following:
If I have a table of values and I do the…
Cratylus
- 52,998
- 69
- 209
- 339
14
votes
5 answers
Date between dates, ignore year
What's the best (fastest) approach to compare if date is in range of dates independently from year?
table "dates":
some_column| since | upto |
-----------|--------------|-------------|
'foo' | '2011-05-01' |…
gadelat
- 1,390
- 1
- 17
- 25
13
votes
3 answers
How to create a SQL using 'between' in Elixir Ecto
I want to create a SQL using the keywork 'between' in Elixir Ecto.
I know how to create a sql using like
where: like(t.descript, ^some_description)
But when I try to do it in the same way as like
where: between(t.start_date, ^start_date,…
王志軍
- 1,021
- 1
- 11
- 21
11
votes
3 answers
Indexing SQL for Between query with only one match?
We have a table with more than two million rows where all queries against it will be a Between lookup using Column1 and Column2. Also, there will only be one possible result. For example...
Col1 Col2
1 5
6 10
11 15
select…
PatrickPL
- 141
- 7