CODEWITHSUNDEEP
mysqldatebetween
Archit
Archit

Reputation: 139

mysql: between not working with date

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.

Upvotes: 4

Views: 10028

Answers (3)

Madhawa Madhuranga
Madhawa Madhuranga

Reputation: 47

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.

Upvotes: 1

Pekka
Pekka

Reputation: 449385

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.

Upvotes: 1

Jacob See
Jacob See

Reputation: 775

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

Upvotes: 2

Related Questions