mysql: between not working with date

Question

I am Writing a Between query with formatted date.. This is my query:

SELECT shop_id, date_format(registered_time,'%d-%m-%Y') as Date FROM shops where (date_format(registered_time,'%d-%m-%Y') BETWEEN '09-03-2016' AND '19-04-2016')

However, when I execute query, it gives my only the records between date 09 and 19 regardless of month. For example, I have records like 30-03-2016, 31-03-2016..but they are ignored.

If anyone can find anything out of this, please tell me..

One more thing is that, I am converting this date from time stamp field. I hope that isn't causing any issues.

You are comparing strings not dates. And '30..' is not between '09..' and '19..'. — Paul Spiegel
– Paul Spiegel, Commented Apr 20, 2016 at 20:15
I am using the same format to compare results there is no issue in it. What dates you have in the table column — shzyincu
– shzyincu, Commented Apr 20, 2016 at 20:44

Jacob See · Accepted Answer · 2016-04-20 19:41:39Z

2

You need to turn your strings back into dates that MySQL understands. From the documentation:

CAST(datetime_col AS DATE)

should help accomplish what you want.

Reference

answered Apr 20, 2016 at 19:41

Jacob See

7656 silver badges17 bronze badges

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

1 Comment

Pekka Over a year ago

CAST() is unlikely to understand the DD-MM-YYYY format though.

Community · Accepted Answer · 2017-05-23 11:54:17Z

1

You can't take two arbitrary strings (like 30-03-2016) and expect BETWEEN to behave like it would for real DATE columns (a behaviour that is hard-coded into mySQL).

You need to use real DATE values for BETWEEN to work properly.

If the columns are already DATE columns, just skip the formatting:

SELECT shop_id, registered_time FROM shops where registered_time 
BETWEEN '2016-03-09' AND '2016-04-19'

If your existing columns are in the DD-MM-YYYY format, convert them to dates using STR_TO_DATE() - either on the fly just for the purposes of this query (sloooowwwww!) or permanently.

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Apr 20, 2016 at 19:40

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Comments

Madhawa Madhuranga · Accepted Answer · 2020-07-03 06:07:40Z

1

You have to turn your "registered_time" string date back to date as follow.

STR_TO_DATE(DATE_FORMAT(registered_time,'%d/%m/%Y'),'%d/%m/%Y') BETWEEN STR_TO_DATE('09-03-2016','%d/%m/%Y') AND STR_TO_DATE('19-04-2016','%d/%m/%Y')

This worked for me.

answered Jul 3, 2020 at 6:07

Madhawa Madhuranga

453 bronze badges

Collectives™ on Stack Overflow

mysql: between not working with date

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related