Mysql Between Date

Question

I have table material :

id : int
title : text
date_operation : date

And i have this data :

    1, title1, 2016-06-02
    2, title2, 2016-06-02
    3, title3, 2016-06-03
    4, title4, 2016-06-03
    5, title6, 2016-06-03
    6, title7, 2016-06-05
    7, title8, 2016-06-05
    8, title9, 2016-06-06
    9, title10, 2016-06-06
    10, title11, 2016-06-06
    11, title11, 2016-06-07
    12, title12, 2016-06-08

I would to dipaly all material where date_operation between 2016-06-02 AND 2016-06-06 :

I try with this :

SELECT * FROM `material` WHERE date_operation = '2016-06-02' AND '2016-06-06'

But show me this result :

1, title1, 2016-06-02
2, title2, 2016-06-02
3, title3, 2016-06-03
4, title4, 2016-06-03
5, title6, 2016-06-03
6, title7, 2016-06-05
7, title8, 2016-06-05

I would like to see this result :

1, title1, 2016-06-02
2, title2, 2016-06-02
3, title3, 2016-06-03
4, title4, 2016-06-03
5, title6, 2016-06-03
6, title7, 2016-06-05
7, title8, 2016-06-05
8, title9, 2016-06-06
9, title10, 2016-06-06
10, title11, 2016-06-06

Devsi Odedra · Answer 1 · 2016-07-22T10:56:06.350

5

Try this

SELECT *
FROM `material`
WHERE (date_operation BETWEEN '2016-06-02' AND '2016-06-06')

edited Jul 22 '16 at 10:56

answered Jul 22 '16 at 10:52

Devsi Odedra

5,244
1
23
37

1

(1) What exactly do the parentheses do? (2) How is the second value interpreted? I don't see this as solving the OP's problem, so additional explanation is helpful. – Gordon Linoff Jul 22 '16 at 10:55

score 1 · Answer 2 · answered Jul 22 '16 at 10:54

You might have a time component on the "date" value. BETWEEN is dangerous with dates. I would recommend:

SELECT *
FROM `material`
WHERE date_operation >= '2016-06-02' AND date_operation < '2016-06-07';

This will work regardless of whether the value has a time component or not.

You could also use the date() function (with between even), but that can impede the use of indexes.

score 0 · Answer 3 · edited Jul 22 '16 at 10:54

0

Try this :

SELECT * FROM `material` WHERE date_operation BETWEEN '2016-06-02 00:00:00' AND '2016-06-06 23:59:59'

edited Jul 22 '16 at 10:54

Vinie

2,983
1
18
29

answered Jul 22 '16 at 10:53

Mehdi Mabrouk

1

score 0 · Answer 4 · answered Jul 22 '16 at 10:53

0

TryThis

SELECT * FROM `material` WHERE date_operation >= '2016-06-02' AND date_operation <='2016-06-06'

answered Jul 22 '16 at 10:53

Sumit patel

3,807
9
34
61

score 0 · Answer 5 · edited Jun 06 '18 at 10:32

0

Best way to use ternary operators to perform MYSQL query fast . Try this :-

`SELECT * FROM material WHERE date_operation >= '2016-06-02' AND date_operation <= '2016-06-06'`;

edited Jun 06 '18 at 10:32

Lavish Tyagi

223
5
10

answered Jul 22 '16 at 11:14

Shyam

280
1
6
17

Mysql Between Date

5 Answers5