Extract only date from datetime field (mysql) and assign it to php variable

Question

I can’t only assign a date to the php variable from the datetime field of the MySQL table. I am using PHP 4 , I know how to do this in PHP 5.

Can someone help with what is missing in the PHP code below.

 $dc = mysql_query("SELECT * FROM INVHDR WHERE Invdate BETWEEN '2011-04-01' AND '2012-03-31'"); while ($dc2 = mysql_fetch_assoc($dc)) { $invno = $dc2['Invno']; // Here Invno is a datetime field type $invdat3 = $dc2['Invdate']; $date_array = explode("-", $invdat3); $invdat = sprintf('%02d/%02d/%4d', $date_array[2], $date_array[1], $date_array[0]); } 

Answer 1

If you just want to display part of the date, you can use the date function:

 echo date("d/m/Y", strtotime("2012-12-24 12:13:14")); 

Answer 2

There is a link that I find extremely useful when it comes to the date of manipulation: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

easy to find a solution there:

 SELECT *, DATE(Invdate) as Inv_date ... 

Answer 3

Do it directly in MySQL:

 select DATE_FORMAT('2012-12-24 12:13:14', '%Y/%m/%d') 

So your query will look like this:

 SELECT DATE_FORMAT(Invdate, '%Y/%m/%d') FROM INVHDR WHERE Invdate BETWEEN '$startdat' AND '$enddat'