4

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.

Archit
  • 139
  • 2
  • 8

3 Answers3

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

Jacob See
  • 755
  • 6
  • 17
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.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
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.

Madhawa Madhuranga
  • 47
  • 3