I wanna select some row between 1 and 3 or between 7 and 10 what's the exact query for this in mysql and laravel too?

Question

SELECT * from jobs where created_at BETWEEN IN('1' and '4', '7' and '10');

this is my sql which i tried but it gives error.thanks in advance for your help

`WHERE created_at BETWEEN 1 AND 4 OR created_at 7 AND 10` should do that. — Qirel, May 11 '17 at 16:59
there is similar question here: http://stackoverflow.com/questions/5268369/multiple-mysql-where-between-clauses — tech2017, May 11 '17 at 17:00

Willem Van Onsem · Answer 1 · 2017-05-11T17:06:47.173

0

You can use two BETWEEN ... AND ... queries with an OR:

SELECT *
FROM jobs
WHERE created_at BETWEEN 1 AND 4
   OR created_at BETWEEN 7 AND 10

edited May 11 '17 at 17:06

answered May 11 '17 at 16:59

Willem Van Onsem

443,496
30
428
555

score 0 · Accepted Answer · answered May 11 '17 at 17:00

You need to separate out your BETWEEN statements:

SELECT * from jobs where created_at BETWEEN 1 and 4 OR created_at BETWEEN 7 AND 10;

For Laravel's Query Builder:

DB::table('jobs')->whereBetween('created_at', [1,4])
    ->orWhereBetween('created_at', [7,10])
    ->get();

score 0 · Answer 3 · answered May 11 '17 at 17:00

0

In Laravel you can use whereBetween and orWhereBetween() methods:

Job::whereBetween('created_at', [1, 4])->orWhereBetween('created_at', [7, 10])->get();

answered May 11 '17 at 17:00

Alexey Mezenin

158,981
26
290
279

score 0 · Answer 4 · answered May 11 '17 at 17:01

0

SELECT * 
FROM jobs 
WHERE created_at BETWEEN 1 and 4 or created_at  BETWEEN 7 and 10;

answered May 11 '17 at 17:01

Dalin Huang

11,212
5
32
49

score 0 · Answer 5 · answered May 11 '17 at 18:11

if you mean by [1, 3, 7, 10] the months, use this:

I guess your model is Job

Job::where(function($q){
    $q->whereMonth('created_at', '>=', 1)
      ->whereMonth('created_at', '<=', 3);
})->orWhere(function($q){
    $q->whereMonth('created_at', '>=', 7)
      ->whereMonth('created_at', '<=', 10);
})->get();

Note1: if mean by numbers the days not months, use whereDay instead of whereMonth Note2: created_at is a timestamp as default

I wanna select some row between 1 and 3 or between 7 and 10 what's the exact query for this in mysql and laravel too?

5 Answers5