0

I want to filter some items based on two different dates using MySQL. In my database I store data like 2017-03-28 10:55:10. But I need only the date part, not the time so I used the DATE() function:

select sum(cashamount) as sumcashsalesamount, DATE(transactiondate) as datepart from master_transaction where transactiondate BETWEEN '2017-02-22%' AND '2017-03-28%' order by transactiondate desc

Above this query have two dates 2017-02-22% and 2017-03-28% but this return no result.

But when I change this 2017-03-28% date to 2017-03-29% (tomorrow date) I get results.

Improve this question
2
  • 1
    Where's the GROUP BY? Commented Mar 28, 2017 at 10:45
  • use date('2017-03-28 00:06:00') to extract date from datetime like this Commented Mar 28, 2017 at 10:48

6 Answers 6

Reset to default
4

Don't use between with "dates". I put that in quotes, because your values are really datetime values.

The best way to write this condition is:

where transactiondate >= '2017-02-22' and transactiondate < '2017-03-29'

Note the inequality for the second condition. BETWEEN is inclusive, so it would include midnight on 2017-03-28.

Why is this best?

  • It allows the query optimizer to take advantage of indexes and partitions.
  • It is exactly the logic that you want.
  • It works for both date and datetime types.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

nice. its use full
2

BETWEEN is inclusive on both sides, and if no time component is given for a date, it defaults to 00:00:00. Since 2017-03-28 10:55:10 > 2017-03-28 00:00:00 it is not included in the result set.

Improve this answer

Comments

1

2017-03-28% = 2017-03-28 00:00:00

If something happened during that day, you need 2017-03-28 23:59:59, or date_sub(2017-03-29, INTERVAL 1 second) for ease

Improve this answer

Comments

1

You can only use wildcard characters (%) with LIKE.

Use:

where date(transactiondate) BETWEEN '2017-02-22' AND '2017-03-28'
Improve this answer

Comments

0

if you use between and you want today, all day, you must put tomorrow date

Improve this answer

Comments

0

And i fix my problem 

select sum(cashamount) as sumcashsalesamount from master_transaction where DATE(transactiondate)BETWEEN '2017-02-22' AND '2017-03-28' order by transactiondate desc

I just re-place the DATE(transactiondate) in where condition its worked

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.