CODEWITHSUNDEEP
javamysqlsqlsql-servermysql-workbench
Hardik Siroya
Hardik Siroya

Reputation: 117

issue in fetching data in MySQL

Everyone, I am having one table named as orders which contains one of its field named date_created. Now I want to fetch data from the particular range of date. I am attaching code:

SELECT * FROM
orders o,
accounts_store a WHERE  o.date_created >= '2018-06-27' AND o.date_created <= '2018-06-28'
    AND a.account_id = '1'
    AND a.store_id = '1'

But this fetches data of the only date 2018-06-27. I am not getting data of 2018-06-28 so what should I do to fetch data of both date? can anyone please help me?

Upvotes: 0

Views: 64

Answers (3)

Gaj
Gaj

Reputation: 886

Try this

SELECT * FROM
orders o,
accounts_store a WHERE  o.date_created >= '2018-06-27' AND DATE_FORMAT(o.date_created, '%Y-%m-%d') <= '2018-06-28'
    AND a.account_id = '1'
    AND a.store_id = '1'

If you have index on date_created then the above query wont use that index. In this better use the below condition o.date_created >= '2018-06-27' AND o.date_created < '2018-06-29'

Upvotes: 0

Meow Meow
Meow Meow

Reputation: 666

To get expected result change the where statement in the query on WHERE o.date_created >= '2018-06-27' AND o.date_created < '2018-06-29'

This problem may arise when the field date_created has type DateTime because an implicit conversation happens. It means that values interpret by DB like WHERE o.date_created >= '2018-06-27 00:00:00:000' AND o.date_created <= '2018-06-28 00:00:00:000'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

The logic in your WHERE clause is off. Use the following to target the 27th and 28th of June:

WHERE o.date_created >= '2018-06-27' AND o.date_created < '2018-06-29'

The problem with o.date_created <= '2018-06-28' is that it means any date on or earlier than midnight on 28 June, i.e. it basically excludes the 28th completely. Instead, I used o.date_created < '2018-06-29', which includes the entire day of 28 June.

Upvotes: 1

Related Questions