Format date column when query is selected

Question

This is my date data; From: 09-01-2013 To: 22-01-2013 (format fixed)

how to format the send_date_time column send_date_time to dmY .. below is my query:

 $filter = "and DATE_FORMAT(send_date_time,'%d-%m-%Y') BETWEEN '08-01-2012 12:00:00' and '22-01-2013 12:00:00' "; 

but the above request fails when I try to execute this (below) request, yes it may work:

 $filter = "and send_date_time BETWEEN '2013-01-08 12:00:00' and '2013-01-22 12:00:00' "; 

Full request

 $get_transaction_history = ("select * from `sms_sendlog` where store_id='".$store."' ".$filter." order by id desc"); 

Column:

 Colum Name : send_date_time eg value : 2012-10-22 10:19:36 

Answer 1

It should be like

 $filter = "and send_date_time BETWEEN DATE_FORMAT('08-01-2012 12:00:00','%d-%m-%Y') AND DATE_FORMAT('22-01-2013 12:00:00','%d-%m-%Y') "; 

More details

Answer 2

Use STR_TO_DATE instead of the DATE_FORMAT function.

Try the following:

 $filter = "and send_date_time BETWEEN STR_TO_DATE('08-01-2012 12:00:00','%d-%m-%Y') AND STR_TO_DATE('22-01-2013 12:00:00','%d-%m-%Y') "; 

Check this link MySQL time function: STR_TO_DATE