Mysql date range does not return correct range

Question

Here is my code:

select date_format(fee_month, '%b-%Y') from bb_fee_info where 
date_format(`FEE_MONTH`,'%b-%Y') >= 'Apr-2016' 
and date_format(`fee_month`, '%b-%Y') <= 'Apr-2017'

As per my understanding above statement should return from April-2016 to April-2017 but it only returns April-2017 records why is that ?

FYI in my Table fee_month stored as date data type and for example, for April month records all my fee_month will be stored as 2017-04-01.

Darshan Mehta · Accepted Answer · 2017-04-16 09:18:07Z

2

Could you try comparing the dates directly instead, e.g.:

SELECT DATE_FORMAT(fee_month, '%b-%Y') 
FROM bb_fee_info 
WHERE fee_month BETWEEN '2016-04-01' AND '2017-04-01';

If you can't change the argument type/format then you can convert it into date using STR_TO_DATE and do the comparison, e.g.:

SELECT DATE_FORMAT(fee_month, '%b-%Y') 
FROM bb_fee_info 
WHERE fee_month BETWEEN STR_TO_DATE(CONCAT('01-', 'Apr-2016'),'%d-%M-%Y') AND STR_TO_DATE(CONCAT('01-', 'Apr-2017'),'%d-%M-%Y')

answered Apr 16, 2017 at 9:18

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Rookie007 Over a year ago

But I want equals part

Darshan Mehta Over a year ago

BETWEEN is inclusive of start and end dates. Do you have time component as well, with dates?

Rookie007 Over a year ago

No time component, but your solution is working fine, but why my query acting weird ?

Darshan Mehta Over a year ago

Try replacing %b with %M?

Strawberry Over a year ago

@Babel This (the first suggestion) is a much smarter solution as it allows the use of indexes

|

Collectives™ on Stack Overflow

Mysql date range does not return correct range

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related