Nrthugu

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 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.

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

Do this:

select col_one,col_two from table_name where date_col between date('2018-11-10') AND date('2018-11-10');

I had the same issue, then I tried this and it worked. Good Luck!