format date column when select query

Question

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

how to format for column date send_date_time to d-m-Y .. 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 above query is failed when i try with this(below) query, yes can work:

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

Full query

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

Column:

Colum Name : send_date_time e.g value : 2012-10-22 10:19:36

Techie · Accepted Answer · 2013-01-09 05:45:41Z

5

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') ";

1 Comment

Thanks @Dasun .. i found we can convert the data first date('Y-m-d',strtotime($date_from)); before put in query too ..

Saharsh Shah · Accepted Answer · 2013-01-09 07:05:51Z

Use STR_TO_DATE function instead of using DATE_FORMAT function.

Try this:

$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') ";