CODEWITHSUNDEEP
phpmysqlsql
iam user
iam user

Reputation: 105

Between date in Mysql not working properly

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.

Upvotes: 0

Views: 409

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 4

iam user
iam user

Reputation: 105

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

Upvotes: 0

slaakso
slaakso

Reputation: 9070

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

Use:

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

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

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.

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

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

Upvotes: 1

Zeljka
Zeljka

Reputation: 376

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

Upvotes: 0

Related Questions