Get records between two date range in MYSQL

Question

I have a table in my mysql db. This is my table.

material       sorg      f_date       t_date
2000012        2100    2016-05-01   2016-05-30
2000013        2100    2016-05-01   2016-05-21
2000021        2200    2016-05-01   2016-05-30
2000151        2100    2016-05-01   2016-05-15
2000336        2300    2016-05-01   2016-05-04
2000366        2300    2016-05-01   2016-05-24
2000451        2400    2016-05-01   2016-05-30
2000493        2200    2016-05-01   2016-05-11

I want to get material between two given date(f_date and t_date).

This is my query...

SELECT tbl_fast_goods.material from tbl_fast_goods WHERE f_date >= '2016-05-23' AND t_date <= '2016-05-29'

The output should be.

2000012, 2000021, 2000366, 2000451

But the problem is no output is given.

In your sample data there is no f_date greater than or equal to 2016-05-23. Your query looks right — Philipp, May 24 '16 at 05:50
I have a list of numbers from 1 to 3. I want to get all of the numbers in this list with a value greater than 4. — Strawberry, May 24 '16 at 06:02

Blank · Accepted Answer · 2016-05-24T06:13:24.603

I think you want this, you just mistook the column.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE tbl
    (`id` int, `user_id` int, `weather_type` varchar(5))
;

INSERT INTO tbl
    (`id`, `user_id`, `weather_type`)
VALUES
    (1, 12, 'cloud'),
    (2, 12, 'rain'),
    (3, 12, 'clear'),
    (4, 14, 'rain'),
    (5, 15, 'clear')
;


CREATE TABLE tbl_fast_goods 
    (`material` int, `sorg` int, `f_date` datetime, `t_date` datetime)
;

INSERT INTO tbl_fast_goods 
    (`material`, `sorg`, `f_date`, `t_date`)
VALUES
    (2000012, 2100, '2016-05-01 00:00:00', '2016-05-30 00:00:00'),
    (2000013, 2100, '2016-05-01 00:00:00', '2016-05-21 00:00:00'),
    (2000021, 2200, '2016-05-01 00:00:00', '2016-05-30 00:00:00'),
    (2000151, 2100, '2016-05-01 00:00:00', '2016-05-15 00:00:00'),
    (2000336, 2300, '2016-05-01 00:00:00', '2016-05-04 00:00:00'),
    (2000366, 2300, '2016-05-01 00:00:00', '2016-05-24 00:00:00'),
    (2000451, 2400, '2016-05-01 00:00:00', '2016-05-30 00:00:00'),
    (2000493, 2200, '2016-05-01 00:00:00', '2016-05-11 00:00:00')
;

Query 1:

SELECT tbl_fast_goods.material
from tbl_fast_goods
WHERE t_date >= '2016-05-23' AND f_date <= '2016-05-29'

Results:

| material |
|----------|
|  2000012 |
|  2000021 |
|  2000366 |
|  2000451 |

thanq it worked. but why should we use like this **t_date >= '2016-05-23' AND f_date <= '2016-05-29'** rather than mine? what was the issue in mine? — D.Madu, May 26 '16 at 07:34
`WHERE t_date >= '2016-05-23' AND f_date <= '2016-05-29'` is different from you. — Blank, May 26 '16 at 07:54

Get records between two date range in MYSQL

1 Answers1